Оконные функции: рейтинг и нарастающий итог

ROW_NUMBER, RANK, LAG и running total — стандарт SQL, работает в PostgreSQL, MySQL 8+, SQL Server.

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

-- Нарастающий итог и сравнение с предыдущим днём
SELECT
    day,
    revenue,
    SUM(revenue) OVER (ORDER BY day)                       AS running_total,
    LAG(revenue) OVER (ORDER BY day)                       AS prev_day,
    revenue - LAG(revenue) OVER (ORDER BY day)             AS delta,
    AVG(revenue) OVER (ORDER BY day
                       ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d
FROM daily_revenue
ORDER BY day;