Window functions: five drills
Five window-function drills, ordered easy to hard. Each isolates one window concept. The default editor shows a working solution - try clearing it and writing your own first; the hidden checks will grade what's there when you press Run.
Tables: users, products, orders.
1. ROW_NUMBER - latest row per group (Easy) #
Task: for each user who has at least one paid order, return their most recent paid order. Output user_id, order_id, order_date, total. One row per user, ordered by user_id. If a user has two paid orders on the same date, the one with the higher id counts as more recent - tie-break with id DESC (user 1 has exactly this situation in the data).
The skill: ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) ranks rows within each group; filtering = 1 keeps the top one.
The trap: without a tie-breaker in the ORDER BY, two rows with the same order_date are non-deterministic - either could win.
2. DENSE_RANK - top 3 with ties (Easy) #
Task: for each product category, list the top 3 most expensive products using DENSE_RANK. Output category, name, price, rank_in_category. Order by category, rank_in_category, name.
The skill: DENSE_RANK shares positions on ties without leaving gaps - so two products tied at #1 both get rank 1, and the next product is rank 2 (not rank 3 like RANK would do).
3. LAG - period-over-period change (Medium) #
Task: for each user, return their order history sorted by date, with the previous order's total alongside the current. Compute the change. Output user_id, order_id, order_date, total, prev_total, change. Order by user_id, order_date, order_id. The first row of each partition has NULL for prev_total and change.
The skill: LAG(col) OVER (PARTITION BY ... ORDER BY ...) looks at the row N back (default 1) within the partition. NULL on the first row of each partition.
4. Running total - cumulative aggregate (Medium) #
Task: for each user, build a running total of their order revenue over time, including the current row. Output user_id, order_id, order_date, total, running_total. Order by user_id, order_date, order_id.
The skill: SUM(col) OVER (PARTITION BY ... ORDER BY ...) with the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) gives a running total. Filter rows out before the window if needed; never after (the running total resets context).
5. QUALIFY - filter a window without a subquery (Hard) #
Task: for each user, return their single latest order of any status, using QUALIFY. Output user_id, order_id, order_date, status, total. One row per user, ordered by user_id. Same-date ties break by id DESC (the higher id wins).
The skill: WHERE cannot reference a window function, so the classic shape wraps the window in a CTE and filters outside. QUALIFY (DuckDB-native, covered at /learn/window-functions/qualify) removes the wrapper: it filters after window functions are computed, right in the same SELECT.
Grading is on the output - the CTE rewrite passes too. But try the QUALIFY form first; it is the version you will reach for in DuckDB, Snowflake, and BigQuery.
The window-function patterns to remember:
ROW_NUMBER= unique 1, 2, 3, ... per partition. Use for "exactly N per group" with a deterministic tie-breaker.RANK= ties share a value, leaves gaps (1, 1, 3, 4). Use when you want to show the rank including ties.DENSE_RANK= ties share a value, no gaps (1, 1, 2, 3). Use for "top N tiers" where ties at the boundary all stay in.LAG(col, n)= the value n rows back in the partition (default 1).NULLon the first n rows.SUM(col) OVER (... ORDER BY ...)= running total. Be explicit aboutROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWto avoid theRANGE-with-ties gotcha.
A window function cannot be used in WHERE directly. Wrap in a CTE / subquery, or use QUALIFY on Snowflake / BigQuery / DuckDB.