SQL window functions: top 3 products per category and running revenue total

-- ======================================================================
-- Q: Window functions: get the 3 most expensive products per category and the
-- cumulative revenue per day.
--
-- ROW_NUMBER gives a unique number, RANK leaves gaps on ties, DENSE_RANK does
-- not. LAG/LEAD compare with the previous row (e.g. price change). In Django:
-- annotate(rn=Window(RowNumber(), partition_by='category',
-- order_by=F('price').desc())), and since Django 4.2 you can filter directly on
-- a window annotation.
-- ======================================================================

-- top 3 per category
SELECT * FROM (
  SELECT p.*, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn
  FROM product p
) t WHERE rn <= 3;

-- cumulative revenue (running total)
SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily_revenue;