Window functions

Window functions let you compute running totals, ranks, lag/lead, and moving averages — analytics that classic GROUP BY can't express. They are the single biggest leap in SQL fluency.

The shape of a window function

SELECT id, customer_id, total_amount,
       SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total
FROM orders;

For each order, the running_total is the sum of that customer's order amounts up to and including the current row. Three things in OVER():

1. PARTITION BY — split the data into groups, like GROUP BY (one running total per customer)
2. ORDER BY — within each group, in what order to compute (so 'running' makes sense)
3. Optionally a frame clause — ROWS BETWEEN ... — to restrict to e.g. the last 7 rows

Ranking functions

SELECT customer_id, created_at,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS order_seq,
       RANK()       OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS amount_rank
FROM orders;

ROW_NUMBER assigns 1, 2, 3 with no ties. RANK assigns 1, 2, 2, 4 — same value gets same rank, then skips. DENSE_RANK is 1, 2, 2, 3 — no skipping. Pick based on what makes sense for the question.

LAG and LEAD

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       revenue - LAG(revenue) OVER (ORDER BY month) AS month_over_month_change
FROM monthly_revenue;

LAG looks back to the previous row; LEAD looks forward. Perfect for period-over-period analysis without joining the table to itself.

Moving averages with frame clauses

SELECT day, revenue,
       AVG(revenue) OVER (
         ORDER BY day 
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_7d_avg
FROM daily_revenue;

7-day trailing moving average. The frame clause tells the engine: include this row and the 6 before it.