Window functions compute across related rows without collapsing the result set—ranking, running totals, and lead/lag comparisons.
ROW_NUMBER and RANK
SELECT
id,
customer_id,
total,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders;Practice: Seed customers/orders tables from earlier lessons, then run queries in psql or DB Fiddle (PostgreSQL).
Running sums
SELECT
created_at::date AS day,
total,
SUM(total) OVER (ORDER BY created_at) AS running_revenue
FROM orders;LAG and LEAD
SELECT
id,
total,
LAG(total) OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_total
FROM orders;Important interview questions and answers
- Q: Window vs GROUP BY?
A: Window keeps detail rows; GROUP BY returns one row per group. - Q: PARTITION BY?
A: Defines groups over which the window frame is applied.
Self-check
- How do you rank orders per customer by date?
- What frame does ORDER BY inside OVER define?
Tip: ROW_NUMBER vs RANK—ties handled differently; pick intentionally.
Interview prep
- PARTITION BY?
Defines groups for window calculations.
- Window vs GROUP BY?
Window keeps detail rows; GROUP BY collapses.