homework/dmedvedev/window functions.sql

--task 1
--Задача 1. Нумерация заказов пользователя

SELECT
	o.user_id AS user_id,
	u.name AS user_name,
	o.id AS order_id, 
	o.order_date AS order_date,
	ROW_NUMBER() OVER (
		PARTITION BY o.user_id
		ORDER BY o.order_date
	) AS order_number_for_user
FROM orders o
JOIN users u ON o.user_id = u.id;

/*
В чём отличие такой нумерации от простой сортировки ORDER BY order_date без оконной функции?

Здесь функция работает внутри определенных строк, в данном случае имени.
*/


--task 2
--Задача 2. Накопительная выручка по пользователю
SELECT 
	o.user_id AS user_id,
	u.name AS user_name,
	o.id AS order_id,
	o.order_date,
	oi.quantity * oi.price_at_purchase AS order_revenue,
	SUM(oi.quantity * oi.price_at_purchase) OVER (
		PARTITION BY o.user_id
		ORDER BY o.order_date
		ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
	) AS running_revenue_for_user
FROM orders o 
JOIN order_items oi ON oi.order_id = o.id
JOIN users u ON u.id = o.user_id;


/*
Почему для накопительной суммы важно задавать рамку окна (от «самого первого» до «текущего»), а не оставлять значения по умолчанию?
Если не задавать рамку окна, то не будет накопительного эффекта, будет просто считаться сумма по строке. И то на самом деле я до конца не понял как она считается, как-будто сумма заказа транслируется на строку с частью заказа.

Как по такой таблице понять, что пользователь начал тратить больше?
Можно дополнительно сделать столбец разности текущего значения от предыдущего и будет видно разницу или же сделать столбец по отклонению от среднего. Правильно мыслю?
*/

--task 3
--Задача 3. Рейтинг пользователей по выручке
WITH total AS (
	SELECT 
		o.user_id,
		SUM(quantity * price_at_purchase) AS total_revenue
	FROM order_items oi 
	JOIN orders o ON o.id = oi.order_id
	GROUP BY o.user_id
)

SELECT 
	u.name AS user_name,
	total_revenue, 
	RANK() OVER (
		ORDER BY total_revenue 
	) AS revenue_rank,
	DENSE_RANK() OVER (
		ORDER BY total_revenue 
	) AS dense_revenue_rank
FROM users u 
JOIN total t ON u.id = t.user_id;
/*
Чем RANK отличается от DENSE_RANK в случае, когда у двух пользователей одинаковая выручка?

При RANK будет одно одинаковое место, при dense будут разные ранги.
*/

--task 4
--Задача 4. Первый и последний заказ пользователя
--task 4
--Задача 4. Первый и последний заказ пользователя

SELECT 
	u.name AS user_name,
	o.id AS order_id,
	o.order_date AS order_date,
	first_value(o.order_date) OVER (
		PARTITION BY o.user_id
		ORDER BY o.order_date
	) AS first_order_date,
	last_value(o.order_date) OVER (
		PARTITION BY o.user_id
		ORDER BY o.order_date
		ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 
	) AS last_order_date
FROM orders o 
JOIN users u ON u.id = o.user_id;

/*
Как можно использовать поля «первый заказ» и «последний заказ» в сегментации клиентов?
Можно понять единый ли это заказ или нет. Если единый, то соответственно, других заказов не было и сравнить с текущей датой и понять давно ли вообще была активность.

Почему для правильного LAST_VALUE важно задать рамку окна так, чтобы смотреть на всю историю пользователя, а не только до текущей строки?
Потому что если не задавать рамку, то функция выдаст предыдущее значение от текущей строки.
*/

/*

*/

--task 5
--Задача 5. Заказы и количество заказов пользователя (без GROUP BY)
SELECT
	u.name AS user_name,
	o.id AS order_id,
	count(*) OVER (
		PARTITION BY u.name
	) AS orders_per_user
FROM users u 
JOIN orders o ON o.user_id = u.id;

/*
Чем такой результат отличается от классического отчёта «по пользователям» с GROUP BY user_id?
GROUP BY уменьшил бы количество строк.
*/

--task 6
--Задача 6. Сравнение суммы заказа с предыдущим
WITH revenue AS (
	SELECT 
		o.id AS order_id,
		o.user_id AS user_id,
		o.order_date AS order_date,
		SUM(oi.quantity * oi.price_at_purchase) AS order_revenue
	FROM orders o 
	JOIN order_items oi ON oi.order_id = o.id
	GROUP BY o.id, o.user_id, o.order_date
)

SELECT 
	u.name,
	order_id,
	order_date,
	order_revenue, 
	LAG(order_revenue) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_order_revenue
FROM revenue r
JOIN users u ON r.user_id = u.id;

/*
Как по такому результату понять, увеличивается или уменьшается средний чек с течением времени?
Можно дополнительно создать еще один столбец в котором будем получать разность текущего и предыдущего заказов, а на основе этого уже делать выводы.
А по такому запросы - думаю наглядно видно в таблице какая сумма больше, а какая меньше.
*/

--task 7
--Задача 7. Категории с накопительной выручкой по времени
WITH category_date_revenue AS (
	SELECT 
		p.category, 
		o.order_date::date AS order_date,
		SUM(oi.price_at_purchase * oi.quantity) AS daily_category_revenue
	FROM products p
	JOIN order_items oi 
		ON p.id = oi.product_id
	JOIN orders o 
		ON oi.order_id = o.id
	
	GROUP BY 
		p.category, 
		o.order_date::date
)

SELECT 
	category AS category,
	order_date::date AS order_date,
	daily_category_revenue,
	SUM(daily_category_revenue) OVER (
		PARTITION BY category
		ORDER BY order_date
		ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
	) AS running_category_revenue
FROM category_date_revenue;


/*
Чем такой «накопительный» график по категории отличается по смыслу от обычного ежедневного (без SUM OVER)?
Видим какая именно категория в какой именно день принесла больше выручки
*/