Part 5 of 5 · 3 min read
Exercises
Easy
Easy 1: Add a per-group count (15 minutes)
Write a query showing every order, each annotated with how many total orders that customer has placed.
Done when: a correct COUNT(*) OVER (PARTITION BY customer_id) query is given.
Hint
SELECT order_id, COUNT(*) OVER (PARTITION BY customer_id) FROM orders;
Easy 2: Rank books by price (15 minutes)
Write a query ranking books from most to least expensive using RANK.
Done when: a correct RANK() OVER (ORDER BY price DESC) query is given.
Hint
SELECT title, price, RANK() OVER (ORDER BY price DESC) FROM books;
Easy 3: Choose ROW_NUMBER or RANK (15 minutes)
State which function to use for assigning a strictly unique position to every row, even when values tie.
Done when: ROW_NUMBER is correctly chosen.
Hint
ROW_NUMBER, since it never repeats a number even for tied values.
Medium
Medium 1: Running total (25 minutes)
Write a query showing a running total of order count per customer, ordered by placed_on.
Done when: a correct running-count query using PARTITION BY and ORDER BY together is given.
Hint
SELECT order_id, customer_id, COUNT(*) OVER (PARTITION BY customer_id ORDER BY placed_on) FROM orders;
Medium 2: Top order per customer (25 minutes)
Using ROW_NUMBER, write a query that could be used to find each customer's single most recent order.
Done when: a correct query assigning ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_on DESC) is given, with a note that filtering to row number 1 (in an outer query) gives the answer.
Hint
Wrap the ROW_NUMBER query in a subquery or CTE, then filter the outer query to WHERE row_num = 1.
Medium 3: RANK versus DENSE_RANK on real data (25 minutes)
Given three books priced 500, 500, and 450, state the RANK and DENSE_RANK values each would receive, ordered highest price first.
Done when: RANK values 1, 1, 3 and DENSE_RANK values 1, 1, 2 are both correctly given.
Hint
RANK gives 1, 1, 3 (skipping 2); DENSE_RANK gives 1, 1, 2 (no gap).
Hard
Hard 1: Window function versus GROUP BY, side by side (30 minutes)
Write two queries answering "each customer's total order count": one using GROUP BY, one using a window function. Explain the difference in how many rows each returns.
Done when: both queries are given correctly, with the GROUP BY version noted as returning one row per customer and the window version noted as returning one row per order.
Hint
GROUP BY returns exactly one row per distinct customer_id; the window version returns one row per order, each showing that customer's total.
Hard 2: Filtering on a window function's result (35 minutes)
Explain why WHERE ROW_NUMBER() OVER (...) = 1 cannot be written directly in a query's WHERE clause, and what has to be done instead.
Done when: the explanation correctly notes that window functions cannot be referenced in WHERE, and that wrapping the query in a subquery or CTE, filtering in the outer query, is the standard fix.
Hint
WHERE is evaluated before window functions are computed, so the database does not yet have a value to filter on; wrapping the query and filtering in an outer layer is the standard fix.
Hard 3: A window frame that isn't the default (40 minutes)
Describe, conceptually, what a window frame of "the current row and the two rows before it" would compute for a moving average, compared to the default "from the start of the partition."
Done when: the explanation correctly distinguishes a fixed-size moving window from the default ever-growing running window.
Hint
A fixed-size frame only looks at a small, sliding set of nearby rows, producing a smoothed local average, while the default running frame keeps growing to include everything from the start.