Chapter 12 — Exercises

Run against mercado. Window functions reward practice — type these and study the output row by row. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.

Group A — Group value alongside each row

12.1 For each product, show its price and the average price of its category on the same row (don't collapse rows). (answer in Appendix)

12.2 Show each product's price, the category average, and the difference between them.

12.3 Show each product's price and its share (%) of the whole catalog's total price (SUM(price) OVER ()).

Group B — Ranking

12.4 Rank products by price within each category using ROW_NUMBER, RANK, and DENSE_RANK side by side. Explain how the three differ on ties. (answer in Appendix)

12.5 Find the single most expensive product in each category (use ROW_NUMBER = 1 via wrap-and-filter). (answer in Appendix)

12.6 Find the two cheapest products in each category.

12.7 ⭐ Divide all products into 4 price quartiles with NTILE(4) and list which quartile each falls in.

Group C — Running totals

12.8 Show payments ordered by date with a running (cumulative) total of amount. (answer in Appendix)

12.9 Show a running count of orders over time (COUNT(*) OVER (ORDER BY order_date)).

12.10 ⭐ Running revenue per customer (partition by customer, order by date).

Group D — LAG / LEAD

12.11 Build monthly revenue (from payments), then add the previous month's revenue with LAG. (answer in Appendix)

12.12 Add month-over-month change and percent change (guard the first month with NULLIF).

12.13 ⭐ For each order per customer (by date), show the days since that customer's previous order (LAG(order_date) + date subtraction).

Group E — Frames

12.14 Compute a 3-day moving average of daily revenue (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW). (answer in Appendix)

12.15 Show each product with the highest-priced product name in its category using FIRST_VALUE.

12.16 ⭐ Demonstrate the LAST_VALUE default-frame surprise: write a LAST_VALUE that returns the current row, then fix it with an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame.

Group F — Fix / explain

12.17 Errors with "window functions are not allowed in WHERE" — why, and how to fix? (answer in Appendix)

SELECT name, ROW_NUMBER() OVER (ORDER BY price DESC) AS rn FROM products WHERE rn <= 3;

12.18 Explain why a running total via window function is far faster than via a correlated subquery (think O(n) vs O(n²)).

12.19 ⭐ When would you use RANK() instead of ROW_NUMBER() for "top per group," and what difference would ties make?

Group G — Progressive project

12.20 Add a top-N-per-group ranking to your domain (e.g., top 3 per category).

12.21 Add a running total and a LAG-based period-over-period comparison.

Self-check. If you can do top-N-per-group (wrap-and-filter), a running total, and a LAG comparison from memory, you wield SQL's most powerful analytics feature. Next: changing data.