--10.1
--Примените оконные функции к таблице products и с помощью ранжирующих функций упорядочьте
--все товары по цене — от самых дорогих к самым дешёвым. Добавьте в таблицу следующие колонки:
--Колонку product_number с порядковым номером товара (функция ROW_NUMBER).
--Колонку product_rank с рангом товара с пропусками рангов (функция RANK).
--Колонку product_dense_rank с рангом товара без пропусков рангов (функция DENSE_RANK)
SELECT name,
price,
product_id,
row_number() OVER (ORDER BY price desc) as product_number,
rank () OVER(ORDER BY price desc) as product_rank,
dense_rank () OVER(ORDER BY price desc) as product_dense_rank
FROM products
--10.2
--для каждой записи проставьте цену самого дорогого товара,
--для каждого товара посчитайте долю его цены в стоимости самого дорогого товара
SELECT product_id,
name,
price,
max(price) OVER () as max_price,
round(price::decimal/max(price) OVER (), 2) as share_of_max
FROM products
ORDER BY price desc, product_id
Вариант верного решения:
SELECT product_id,
name,
price,
max(price) OVER () as max_price,
round(price / max(price) OVER (), 2) as share_of_max
FROM products
ORDER BY price desc, product_id
--10.3
--для вычисления максимальной и минимальной цены
SELECT product_id,
name,
price,
max(price) OVER (ORDER BY price desc) as max_price,
min(price) OVER (ORDER BY price desc) as min_price
FROM products
ORDER BY price desc, product_id
--10.4
--на основе таблицы orders сформируйте новую таблицу с общим числом заказов по дням
-- поместите полученную таблицу в подзапрос и примените к ней оконную функцию в паре с
--агрегирующей функцией SUM для расчёта накопительной суммы числа заказов
--Не забудьте для окна задать инструкцию ORDER BY по дате
SELECT date,
orders_count,
(sum(orders_count) OVER (ORDER BY date))::integer as orders_cum_count
FROM (SELECT creation_time::date as date,
count(distinct order_id) as orders_count
FROM orders
INNER JOIN (SELECT order_id
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) as t1 using (order_id)
GROUP BY creation_time::date) as t2
--Вариант верного решения:
SELECT date,
orders_count,
sum(orders_count) OVER (ORDER BY date)::integer as orders_cum_count
FROM (SELECT date(creation_time) as date,
count(order_id) as orders_count
FROM orders
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')
GROUP BY date) t
--10.5
--Для каждого пользователя в таблице user_actions посчитайте порядковый номер каждого заказа
--Для этого примените оконную функцию ROW_NUMBER к колонке с временем заказа
SELECT user_id,
order_id,
time,
row_number() OVER (PARTITION BY user_id
ORDER BY time) as order_number
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')
ORDER BY user_id, order_number limit 1000
--10.6
SELECT LAG(column, 1) OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE BETWEEN ...) AS lag_value
FROM table
SELECT LEAD(column, 1) OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE BETWEEN ...) AS lead_value
FROM table
--В качестве первого аргумента у функций LAG и LEAD указывается колонка со значениями,
--в качестве второго — то, на какое число строк производить смещение (назад и вперёд соответственно).
--Второй аргумент можно не указывать, по умолчанию его значение равно 1.
--ополните запрос из предыдущего задания и с помощью оконной функции для каждого заказа каждого пользователя рассчитайте,
--сколько времени прошло с момента предыдущего заказа
SELECT user_id,
order_id,
time,
row_number() OVER (PARTITION BY user_id
ORDER BY time) as order_number,
lag(time, 1) OVER (PARTITION BY user_id
ORDER BY time) as time_lag,
time - lag(time, 1) OVER (PARTITION BY user_id
ORDER BY time) as time_diff
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')
ORDER BY user_id, order_number limit 1000
--10.7
--для каждого пользователя рассчитайте, сколько в среднем времени проходит между его заказами.
--Посчитайте этот показатель только для тех пользователей,
--которые за всё время оформили более одного неотмененного заказа
SELECT user_id,
avg(time_diff)::integer as hours_between_orders
FROM (SELECT user_id,
order_id,
time,
extract(epoch
FROM (time - lag(time, 1)
OVER (
PARTITION BY user_id
ORDER BY time)))/3600 as time_diff
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t
WHERE time_diff is not null
GROUP BY user_id
ORDER BY user_id limit 1000
--10.8
--сформируйте новую таблицу с общим числом заказов по дням
--примените к ней оконную функцию в паре с агрегирующей функцией AVG для расчёта скользящего среднего
--числа заказов.
--Скользящее среднее для каждой записи считайте по трём предыдущим дням
SELECT
date,
orders_count,
ROUND(AVG(orders_count) OVER (ORDER BY date ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING), 2) AS moving_avg
FROM
(SELECT
DATE(creation_time) AS date,
COUNT(order_id) AS orders_count
FROM
orders
WHERE
order_id NOT IN (SELECT order_id FROM user_actions WHERE action = 'cancel_order')
GROUP BY
DATE(creation_time)
) AS t
ORDER BY
date;
--Вариант верного решения:
SELECT date,
orders_count,
round(avg(orders_count) OVER (ORDER BY date rows between 3 preceding and 1 preceding),
2) as moving_avg
FROM (SELECT date(creation_time) as date,
count(order_id) as orders_count
FROM orders
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')
GROUP BY date) t
--10.9
--тех курьеров, которые доставили в сентябре 2022 года заказов больше, чем в среднем все курьеры
SELECT courier_id,
delivered_orders,
round(avg(delivered_orders) OVER(), 2) as avg_delivered_orders,
case when delivered_orders > round(avg(delivered_orders) OVER(), 2) then '1'
else '0' end as is_above_avg
FROM (SELECT courier_id,
count(order_id) as delivered_orders
FROM courier_actions
WHERE action = 'deliver_order'
and date_part('month', time) = 9
and date_part('year', time) = 2022
GROUP BY courier_id
ORDER BY courier_id) as t1
--10.10
--По данным таблицы user_actions посчитайте число первых и повторных заказов на каждую дату
SELECT time::date as date,
order_type,
count(order_id) as orders_count
FROM (SELECT user_id,
order_id,
time,
case when time = min(time) OVER (PARTITION BY user_id) then 'Первый'
else 'Повторный' end as order_type
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t
GROUP BY date, order_type
ORDER BY date, order_type
--10.11
--К запросу, полученному на предыдущем шаге, примените оконную функцию и
--для каждого дня посчитайте долю первых и повторных заказов
SELECT date,
order_type,
orders_count,
ROUND(orders_count/SUM(orders_count) OVER (PARTITION BY date), 2) AS orders_share
FROM
(SELECT time::date as date,
order_type,
count(order_id) as orders_count
FROM (SELECT user_id,
order_id,
time,
case when time = min(time) OVER (PARTITION BY user_id) then 'Первый'
else 'Повторный' end as order_type
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t
GROUP BY date, order_type
ORDER BY date, order_type) t1
Вариант верного решения:
SELECT date,
order_type,
orders_count,
round(orders_count / sum(orders_count) OVER (PARTITION BY date),
2) as orders_share
FROM (SELECT time::date as date,
order_type,
count(order_id) as orders_count
FROM (SELECT user_id,
order_id,
time,
case when time = min(time) OVER (PARTITION BY user_id) then 'Первый'
else 'Повторный' end as order_type
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t
GROUP BY date, order_type) t
ORDER BY date, order_type
--10.12
--среднюю цену всех товаров. Колонку с этим значением назовите avg_price.
--Затем с помощью оконной функции и оператора FILTER в отдельной колонке
--рассчитайте среднюю цену товаров без учёта самого дорогого.
SELECT product_id,
name,
price,
round(avg_p, 2) as avg_price,
round(avg_p_f, 2) as avg_price_filtered
FROM (SELECT product_id,
name,
price,
avg(price) OVER() as avg_p,
avg(price) filter (WHERE product_id != 13) OVER() as avg_p_f
FROM products) as t1
ORDER BY price desc, product_id
--Вариант верного решения:
SELECT product_id,
name,
price,
round(avg(price) OVER (), 2) as avg_price,
round(avg(price) filter (WHERE price != (SELECT max(price)
FROM products))
OVER (), 2) as avg_price_filtered
FROM products
ORDER BY price desc, product_id
--Оконка внутри ROUND, т.к. "round is not a window function nor an aggregate function"
--10.13
--Для каждой записи в таблице user_actions с помощью оконных функций и предложения FILTER посчитайте,
--сколько заказов сделал и сколько отменил каждый пользователь на момент совершения нового действия.
--Иными словами, для каждого пользователя в каждый момент времени посчитайте две накопительные суммы —
--числа оформленных и числа отменённых заказов
--На основе этих двух колонок для каждой записи пользователя посчитайте показатель cancel_rate,
--т.е. долю отменённых заказов в общем количестве оформленных заказов. Значения показателя
--округлите до двух знаков после запятой
SELECT user_id,
order_id,
action,
time,
COUNT(order_id) filter (WHERE action != 'cancel_order') OVER (PARTITION BY user_id ORDER BY time) AS created_orders,
COUNT(order_id) filter (WHERE action = 'cancel_order') OVER (PARTITION BY user_id ORDER BY time) AS canceled_orders,
round((COUNT(order_id) filter (WHERE action = 'cancel_order') OVER (PARTITION BY user_id ORDER BY time))::decimal /
(COUNT(order_id) filter (WHERE action != 'cancel_order') OVER (PARTITION BY user_id ORDER BY time)), 2) as cancel_rate
FROM user_actions
ORDER BY user_id, order_id, time
LIMIT 1000
--Вариант верного решения:
SELECT user_id,
order_id,
action,
time,
created_orders,
canceled_orders,
round(canceled_orders::decimal / created_orders, 2) as cancel_rate
FROM (SELECT user_id,
order_id,
action,
time,
count(order_id) filter (WHERE action != 'cancel_order') OVER (PARTITION BY user_id
ORDER BY time) as created_orders,
count(order_id) filter (WHERE action = 'cancel_order') OVER (PARTITION BY user_id
ORDER BY time) as canceled_orders
FROM user_actions) t
ORDER BY user_id, order_id, time limit 1000
--10.14
--Из таблицы courier_actions отберите топ 10% курьеров по количеству доставленных за всё время заказов
SELECT courier_id,
orders_count,
row_number() OVER (ORDER BY orders_count desc, courier_id) as courier_rank
FROM(SELECT courier_id,
count(order_id) as orders_count
FROM courier_actions
WHERE action = 'deliver_order'
GROUP BY courier_id) t1 limit round((SELECT count(distinct courier_id)
FROM courier_actions)*0.1)
Вариант верного решения:
with courier_count as (SELECT count(distinct courier_id)
FROM courier_actions)
SELECT courier_id,
orders_count,
courier_rank
FROM (SELECT courier_id,
count(distinct order_id) as orders_count,
row_number() OVER (ORDER BY count(distinct order_id) desc, courier_id) as courier_rank
FROM courier_actions
WHERE action = 'deliver_order'
GROUP BY courier_id) as t1
WHERE courier_rank <= round((SELECT *
FROM courier_count)*0.1)
--10.15
--отберите из таблицы courier_actions всех курьеров, которые работают в нашей компании 10 и более дней.
--Также рассчитайте, сколько заказов они уже успели доставить за всё время работы.
with courier_working_days as (SELECT courier_id,
min("time") OVER (PARTITION BY courier_id) as first_action_time,
max("time") OVER () as last_action_time,
count(*) filter (WHERE action = 'deliver_order') OVER (PARTITION BY courier_id) as delivered_orders
FROM courier_actions)
SELECT courier_id,
round(date_part('day', last_action_time - first_action_time))::int as days_employed,
delivered_orders
FROM courier_working_days
GROUP BY courier_id, first_action_time, last_action_time, delivered_orders having date_part('day', last_action_time - first_action_time) >= 10
ORDER BY days_employed desc, courier_id
Вариант верного решения:
SELECT courier_id,
days_employed,
delivered_orders
FROM (SELECT courier_id,
delivered_orders,
date_part('days', max(max_time) OVER() - min_time)::integer as days_employed
FROM (SELECT courier_id,
count(distinct order_id) filter (WHERE action = 'deliver_order') as delivered_orders,
min(time) as min_time,
max(time) as max_time
FROM courier_actions
GROUP BY courier_id) t1) t2
WHERE days_employed >= 10
ORDER BY days_employed desc, courier_id
--10.16
--На основе информации в таблицах orders и products рассчитайте стоимость каждого заказа, ежедневную выручку сервиса и долю стоимости каждого заказа в ежедневной выручке, выраженную в процентах.
--В результат включите следующие колонки: id заказа, время создания заказа, стоимость заказа, выручку за день, в который был совершён заказ, а также долю стоимости заказа в выручке за день, выраженную в процентах.
--При расчёте долей округляйте их до трёх знаков после запятой.
--Результат отсортируйте сначала по убыванию даты совершения заказа (именно даты, а не времени), потом по убыванию доли заказа в выручке за день, затем по возрастанию id заказа.
--При проведении расчётов отменённые заказы не учитывайте.
SELECT order_id,
creation_time,
order_price,
sum (order_price) OVER (PARTITION BY date_part ('days', creation_time)) as daily_revenue,
round (order_price / sum (order_price) OVER (PARTITION BY date_part ('days', creation_time))*100, 3) as percentage_of_daily_revenue
FROM (SELECT order_id,
creation_time,
sum (price) as order_price FROM(SELECT order_id,
creation_time,
unnest (product_ids) as product_id
FROM orders
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t
LEFT JOIN products using (product_id)
GROUP BY order_id, creation_time) t1
ORDER BY creation_time::date desc, percentage_of_daily_revenue desc, order_id;
--Вариант верного решения:
SELECT order_id,
creation_time,
order_price,
sum(order_price) OVER(PARTITION BY date(creation_time)) as daily_revenue,
round(100 * order_price::decimal / sum(order_price) OVER(PARTITION BY date(creation_time)),
3) as percentage_of_daily_revenue
FROM (SELECT order_id,
creation_time,
sum(price) as order_price
FROM (SELECT order_id,
creation_time,
product_ids,
unnest(product_ids) as product_id
FROM orders
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t3
LEFT JOIN products using(product_id)
GROUP BY order_id, creation_time) t
ORDER BY date(creation_time) desc, percentage_of_daily_revenue desc, order_id;
--10.17
--На основе информации в таблицах orders и products рассчитайте ежедневную выручку сервиса и отразите её в колонке daily_revenue.
--Затем с помощью оконных функций и функций смещения посчитайте ежедневный прирост выручки.
--Прирост выручки отразите как в абсолютных значениях, так и в % относительно предыдущего дня.
--Колонку с абсолютным приростом назовите revenue_growth_abs, а колонку с относительным — revenue_growth_percentage.
--Для самого первого дня укажите прирост равным 0 в обеих колонках. При проведении расчётов отменённые заказы не учитывайте.
--Результат отсортируйте по колонке с датами по возрастанию.
--Метрики daily_revenue, revenue_growth_abs, revenue_growth_percentage округлите до одного знака при помощи ROUND().
SELECT date,
sum (price) as daily_revenue,
coalesce (round (sum (price) - lag (sum (price), 1) OVER (ORDER BY date), 1), 0) as revenue_growth_abs,
coalesce (round (100*sum (price)::decimal / lag (sum (price), 1) OVER (ORDER BY date) - 100, 1), 0) as revenue_growth_percentage
FROM (SELECT order_id,
creation_time::date as date,
unnest(product_ids) as product_id
FROM orders
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t1
LEFT JOIN products using(product_id)
GROUP BY date
ORDER BY date;
--Вариант верного решения:
SELECT date,
round(daily_revenue, 1) as daily_revenue,
round(coalesce(daily_revenue - lag(daily_revenue, 1) OVER (ORDER BY date), 0),
1) as revenue_growth_abs,
round(coalesce(round((daily_revenue - lag(daily_revenue, 1) OVER (ORDER BY date))::decimal / lag(daily_revenue, 1) OVER (ORDER BY date) * 100, 2), 0),
1) as revenue_growth_percentage
FROM (SELECT date(creation_time) as date,
sum(price) as daily_revenue
FROM (SELECT order_id,
creation_time,
product_ids,
unnest(product_ids) as product_id
FROM orders
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t1
LEFT JOIN products using(product_id)
GROUP BY date) t2
ORDER BY date;
--10.18
--С помощью оконной функции рассчитайте медианную стоимость всех заказов из таблицы orders,
--оформленных в нашем сервисе. В качестве результата выведите одно число.
--Колонку с ним назовите median_price. Отменённые заказы не учитывайте.
--Поле в результирующей таблице: median_price
WITH order_prices AS (SELECT
o.order_id,
SUM(p.price) AS total_price
FROM
orders o
JOIN
products p
ON
p.product_id = ANY(o.product_ids)
GROUP BY
o.order_id),
ordered_prices AS (SELECT
total_price,
ROW_NUMBER() OVER (ORDER BY total_price) AS row_num,
COUNT(*) OVER () AS total_count
FROM
order_prices)
SELECT
CASE
WHEN total_count % 2 = 1 THEN
(SELECT total_price
FROM ordered_prices
WHERE row_num = (total_count + 1) / 2)
ELSE
(SELECT AVG(total_price)
FROM ordered_prices
WHERE row_num IN (total_count / 2, total_count / 2 + 1))
END AS median_price
FROM
ordered_prices
LIMIT 1;
--или
WITH main_table AS (SELECT
order_price,
ROW_NUMBER() OVER (ORDER BY order_price) AS row_number,
COUNT(*) OVER() AS total_rows
FROM (SELECT
SUM(price) AS order_price
FROM (SELECT
order_id,
product_ids,
UNNEST(product_ids) AS product_id
FROM
orders
WHERE
order_id NOT IN (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t3
LEFT JOIN products USING(product_id)
GROUP BY order_id) t1)
SELECT
AVG(order_price) AS median_price
FROM
main_table
WHERE
row_number BETWEEN total_rows / 2.0 AND total_rows / 2.0 + 1;
To embed this program on your website, copy the following code and paste it into your website's HTML: