SQL for Product Managers
Window Functions, Explained Without Fear
A window function lets you calculate something across a set of related rows without collapsing them into one row, the way GROUP BY does. That single distinction unlocks running totals, rankings, and period-over-period comparisons.
SELECT
DATE_TRUNC('week', order_date) AS week,
SUM(amount) AS weekly_revenue,
AVG(SUM(amount)) OVER (
ORDER BY DATE_TRUNC('week', order_date)
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
) AS rolling_4week_avg
FROM orders
GROUP BY 1
ORDER BY 1;
The key phrase is OVER (...), which defines the "window," the set of rows the calculation should look across, without merging those rows into a single output row the way a plain GROUP BY would. Here, for each week, it's calculating the average of that week and the three weeks before it, a rolling four-week average, while still showing every individual week's own revenue right alongside it.
Window function | What it does | Common use |
|---|---|---|
ROW_NUMBER() | Assigns a unique sequential number within a group | Finding each user's first order |
RANK() | Assigns a rank, with ties sharing a rank and a gap after | Leaderboards, "top N per category" |
LAG() / LEAD() | Looks at the previous or next row's value | Week-over-week or month-over-month change |
SUM() OVER (...) | Running or rolling totals | Cumulative revenue, rolling averages |
A genuinely common PM use case: finding each user's first order, without accidentally grouping away all their other orders.
SELECT user_id, order_date, amount
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date ASC) AS rn
FROM orders
) ranked
WHERE rn = 1;
PARTITION BY user_id is doing here what GROUP BY would do, restart the count for each user, except instead of collapsing each user into one output row, it just tags every row with its rank within that user's own order history, letting you then filter to rn = 1 for "just the first order," while keeping the full row of detail intact.
YOUR MOVE THIS WEEK
Find one metric your team currently calculates as a static snapshot ("total users today") and ask what it would look like as a rolling trend instead ("7-day rolling average of daily active users"). That's the exact shift window functions are built for.