Задача 1
Напишите запрос, который проранжирует расходы на привлечение пользователей за каждый день по убыванию.
Выгрузите три поля:
- дата, которую нужно привести к типу date;
- расходы на привлечение;
- ранг строки.
SELECT CAST(created_at AS date), costs,
ROW_NUMBER() OVER(ORDER BY costs DESC)
FROM tools_shop.costs
Задача 2
Измените предыдущий запрос: записям с одинаковыми значениями расходов назначьте одинаковый ранг. Ранги не должны прерываться.
SELECT CAST(created_at AS date), costs,
DENSE_RANK() OVER(ORDER BY costs DESC)
FROM tools_shop.costs
Задача 3
Используя оконную функцию, выведите список уникальных user_id пользователей, которые совершили три заказа и более.
WITH rang_users AS
(SELECT *, RANK() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
FROM tools_shop.orders
)
SELECT DISTINCT(user_id)
FROM rang_users
WHERE rn = 3
Задача 4
Используя оконную функцию, выведите количество заказов, в которых было четыре товара и более.
WITH items AS
(SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id) AS rn
FROM tools_shop.order_x_item
)
SELECT COUNT(*)
FROM items
WHERE rn = 4
Задача 5
Рассчитайте количество зарегистрированных пользователей по месяцам с накоплением.
Выгрузите два поля:
- месяц регистрации, приведённый к типу date;
- общее количество зарегистрированных пользователей на текущий месяц.
SELECT
DISTINCT(CAST(DATE_TRUNC('month', created_at) AS date)),
COUNT(user_id) OVER (ORDER BY CAST(DATE_TRUNC('month', created_at) AS date))
FROM tools_shop.users
Задача 6
Рассчитайте сумму трат на привлечение пользователей с накоплением по месяцам c 2017 по 2018 год включительно.
Выгрузите два поля:
- месяц, приведённый к типу date;
- сумма трат на текущий месяц с накоплением.
SELECT
DISTINCT(CAST(DATE_TRUNC('month', created_at) AS date)),
SUM(costs) OVER (ORDER BY CAST(DATE_TRUNC('month', created_at) AS date))
FROM tools_shop.costs
WHERE CAST(created_at AS date) BETWEEN '2017-01-01' AND '2018-12-31'
Задача 7
Посчитайте события с названием view_item по месяцам с накоплением. Рассчитайте количество событий только для тех пользователей, которые совершили хотя бы одну покупку.
Выгрузите поля:
- месяц события, приведённый к типу date;
- количество событий за текущий месяц;
- количество событий за текущий месяц с накоплением.
WITH
buyers AS
(
SELECT DISTINCT(user_id)
FROM tools_shop.orders
),
views AS
(
SELECT
DISTINCT(CAST(DATE_TRUNC('month', event_time) AS date)) AS month,
COUNT(events) OVER (PARTITION BY CAST(DATE_TRUNC('month', event_time) AS date)) AS count
FROM tools_shop.events
WHERE event_name LIKE 'view_item' AND user_id IN (SELECT * FROM buyers)
)
SELECT *, SUM(count) OVER (ORDER BY CAST(DATE_TRUNC('month', month) AS date))
FROM views
Задача 8
Используя конструкцию WINDOW, рассчитайте суммарную стоимость и количество заказов с накоплением от месяца к месяцу.
Выгрузите поля:
- идентификатор заказа;
- месяц оформления заказа, приведённый к типу date;
- сумма заказа;
- количество заказов с накоплением;
- суммарная стоимость заказов с накоплением.
SELECT order_id, CAST(DATE_TRUNC('month', created_at) AS date), total_amt,
COUNT(total_amt) OVER my_window,
SUM(total_amt) OVER my_window
FROM tools_shop.orders
WINDOW my_window AS (ORDER BY CAST(DATE_TRUNC('month', created_at) AS date))
Задача 9
Напишите запрос, который выведет сумму трат на привлечение пользователей по месяцам, а также разницу в тратах между текущим и предыдущим месяцами. Разница должна показывать, на сколько траты текущего месяца отличаются от предыдущего. В случае, если данных по предыдущему месяцу нет, укажите ноль.
Выгрузите поля:
- месяц, приведённый к типу date;
- траты на привлечение пользователей в текущем месяце;
- разница в тратах между текущим и предыдущим месяцами.
WITH
agg AS (SELECT CAST(DATE_TRUNC('month', created_at) AS date) AS month, SUM(costs) AS costs
FROM tools_shop.costs
GROUP BY CAST(DATE_TRUNC('month', created_at) AS date)
ORDER BY CAST(DATE_TRUNC('month', created_at) AS date)
)
SELECT *,
costs - LAG(costs,1, costs) OVER () AS delta
FROM agg
Задача 10
Напишите запрос, который выведет сумму выручки по годам и разницу выручки между текущим и следующим годом. Разница должна показывать, на сколько выручка следующего года отличается от текущего. В случае, если данных по следующему году нет, укажите ноль.
Выгрузите поля:
- год, приведённый к типу date;
- выручка за текущий год;
- разница в выручке между текущим и следующим годом.
WITH
agg AS (SELECT CAST(DATE_TRUNC('year', paid_at) AS date) AS year, SUM(total_amt) AS total
FROM tools_shop.orders
GROUP BY CAST(DATE_TRUNC('year', paid_at) AS date)
ORDER BY CAST(DATE_TRUNC('year', paid_at) AS date)
)
SELECT *,
(LEAD(total,1, total) OVER ()) - total AS delta
FROM agg