Why window functions
Window functions let you compute metrics across related rows while keeping each row.
Common uses:
- Rank customers by revenue
- Running totals over time
- Moving averages
ROW_NUMBER and RANK
SELECT
user_id,
order_id,
amount,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
FROM orders;Running total
SELECT
order_date,
daily_revenue,
SUM(daily_revenue) OVER (ORDER BY order_date) AS running_revenue
FROM (
SELECT
DATE(order_ts) AS order_date,
SUM(amount) AS daily_revenue
FROM orders
GROUP BY DATE(order_ts)
) t
ORDER BY order_date;PARTITION BY
- Splits data into groups
- Window function works within each group
Example: running revenue by country.
Window functions vs GROUP BY
The key difference from GROUP BY: a window function does not collapse rows. Every input
row stays in the result, but each row also gets a computed value based on the rows around it
(its "window").
flowchart LR
A["All rows"] --> B{"PARTITION BY user_id"}
B --> C["Window: user 1's rows"]
B --> D["Window: user 2's rows"]
C --> E["ROW_NUMBER / SUM ... per row"]
D --> E
E --> F["Same row count as input, plus a new column"]
Check your understanding
What is the key difference between a window function and GROUP BY?
GROUP BY collapses rows, while a window function adds a computed value to each row.
Show answer
A — A window function keeps every input row — GROUP BY collapses rows, while a window function adds a computed value to each row.
What does ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) return?
PARTITION BY restarts the numbering for each user, and ordering by amount descending gives the largest order rn = 1.
Show answer
A — A rank of each order within its user, 1 for the largest amount — PARTITION BY restarts the numbering for each user, and ordering by amount descending gives the largest order rn = 1.
What happens if you forget PARTITION BY in a per-user running total?
Without PARTITION BY the whole table is one window.
Show answer
A — It quietly becomes a global total across the table — Without PARTITION BY the whole table is one window.
How is the running revenue computed in the lesson?
A SUM window ordered by date accumulates daily revenue from the first date to the current row.
Show answer
A — SUM(daily_revenue) OVER (ORDER BY order_date) — A SUM window ordered by date accumulates daily revenue from the first date to the current row.
🧪 Try It Yourself
Exercise 1 – ROW_NUMBER per partition
Tab indents. Press Escape, then Tab, to leave the editor. Control plus Enter runs the code; Control plus Shift plus Enter submits it.
Press Run to see what your code prints.Exercise 2 – Running total with SUM() OVER
Tab indents. Press Escape, then Tab, to leave the editor. Control plus Enter runs the code; Control plus Shift plus Enter submits it.
Press Run to see what your code prints.Exercise 3 – RANK vs ROW_NUMBER on ties
Tab indents. Press Escape, then Tab, to leave the editor. Control plus Enter runs the code; Control plus Shift plus Enter submits it.
Press Run to see what your code prints.Next
CTEs (WITH) and Subqueries show you how to name and reuse the derived tables you've been building inline with subqueries so far.