Lesson 11: Window Functions: ROW_NUMBER, RANK, LAG/LEAD

Window functions compute a value across a set of related rows without collapsing them into one row — ranking and running totals without losing detail.

Real-time example

Rank each customer's orders from most to least expensive, without merging all their orders into one summary row.

SELECT
    customer_id,
    amount,
    RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rank_in_customer
FROM orders;

What's happening

PARTITION BY restarts the ranking for each customer separately — unlike GROUP BY, every individual order row is still returned.

LAG/LEAD are the go-to for 'compare this row to the previous/next row' — e.g. day-over-day revenue change.