Window Functions: ROW_NUMBER, RANK, LAG, LEAD, PARTITION BY?

-- sales(region, manager, amount)
-- GROUP BY: одна строка на регион, детали потеряны
SELECT region, SUM(amount) FROM sales GROUP BY region;

-- Окно: каждая строка остаётся, рядом — сумма по региону
SELECT region, manager, amount,
       SUM(amount) OVER (PARTITION BY region)                 AS region_total,
       ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY region), 1) AS pct_of_region
FROM sales;

SELECT manager, amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
       RANK()       OVER (ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rnk,
       NTILE(2)     OVER (ORDER BY amount DESC) AS half
FROM sales;

-- 3 самых дорогих заказа каждого клиента
SELECT *
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC, id) AS rn
    FROM orders o
) t
WHERE rn <= 3;

-- Оставить последнюю запись по каждому email, удалить остальные
DELETE FROM subscribers
WHERE id IN (
    SELECT id FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY lower(email) ORDER BY created_at DESC) AS rn
        FROM subscribers
    ) d
    WHERE rn > 1
);

-- Выручка по месяцам и изменение к предыдущему месяцу
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month)                      AS prev_revenue,
       revenue - LAG(revenue) OVER (ORDER BY month)            AS diff,
       ROUND(100.0 * (revenue / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) - 1), 1) AS growth_pct,
       LEAD(revenue) OVER (ORDER BY month)                     AS next_revenue
FROM monthly_revenue;

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day)                                        AS running_total,
       AVG(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

-- Найти непрерывные серии дней, когда пользователь заходил
SELECT user_id, MIN(day) AS streak_start, MAX(day) AS streak_end, COUNT(*) AS days
FROM (
    SELECT user_id, day,
           day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day))::int AS grp   -- у подряд идущих дней разность постоянна
    FROM logins
) t
GROUP BY user_id, grp
HAVING COUNT(*) >= 7;          -- серии от 7 дней