Practice Gym: Windows and CTEs
The power tools.
The last round of the gym. Window functions and CTEs. These felt hard at first. Let's prove they aren't any more. The trick is always the same. Break the question into steps, and give each step a name.

- Window functions and CTEs
- Not hard any more
- Break it into named steps
First customer per country
Who was the very first customer to join in each country? Pause and try. Here's a solution. Row number, partitioned by country, ordered by the join date. Then keep number one. Rosa Khan, from Canada, was Helen's first customer of all, in January twenty twenty three.
The first customer to join in each country
Show hint
ROW_NUMBER() OVER (PARTITION BY country ORDER BY joined)
Show solution
WITH ranked AS (
SELECT country, first_name, last_name, joined,
ROW_NUMBER() OVER (PARTITION BY country
ORDER BY joined) AS rn
FROM customers
)
SELECT country, first_name, last_name, joined
FROM ranked
WHERE rn = 1
ORDER BY joined;โโโโโโโโโโโโโฌโโโโโโโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโโโโโ โ country โ first_name โ last_name โ joined โ โโโโโโโโโโโโโผโโโโโโโโโโโโโผโโโโโโโโโโโโผโโโโโโโโโโโโโค โ Canada โ Rosa โ Khan โ 2023-01-04 โ โ Australia โ Leo โ Okafor โ 2023-01-07 โ โ UK โ Emma โ Silva โ 2023-01-10 โ โ USA โ Isla โ Dubois โ 2023-01-12 โ โ Ireland โ Umar โ Ahmed โ 2023-01-16 โ โ Germany โ Zane โ Okafor โ 2023-03-23 โ โโโโโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโโโโโโดโโโโโโโโโโโโโ
Best seller per genre
What's the best selling book in each genre, of all time? Pause and try. Here's a solution. One CTE adds up the copies, a second ranks them inside each genre, then keep rank one. Dragons of Ember Bay leads fantasy, and the whole shop.
The best-selling book in each genre
Show hint
CTE for copies, then RANK() OVER (PARTITION BY genre ...)
Show solution
WITH sold AS (
SELECT b.genre, b.title, SUM(oi.quantity) AS copies
FROM books AS b
JOIN order_items AS oi ON oi.book_id = b.book_id
GROUP BY b.book_id
),
ranked AS (
SELECT *, RANK() OVER (PARTITION BY genre
ORDER BY copies DESC) AS rnk
FROM sold
)
SELECT genre, title, copies
FROM ranked
WHERE rnk = 1
ORDER BY copies DESC;โโโโโโโโโโโโโโฌโโโโโโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโโ โ genre โ title โ copies โ โโโโโโโโโโโโโโผโโโโโโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโโค โ Fantasy โ Dragons of Ember Bay โ 212 โ โ Mystery โ The Silent Orchard โ 179 โ โ Thriller โ Code of the River โ 172 โ โ Romance โ Letters to Lisbon โ 116 โ โ Technology โ SQL in a Weekend โ 113 โ โ Business โ Numbers Don't Lie โ 104 โ โ Self-help โ Running on Empty โ 98 โ โ Cooking โ One Pan Dinners โ 58 โ โ History โ A Short History of Bread โ 56 โ โโโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโโ
Quick check
Quick check. Two books in a genre tie for the most copies. You want both of them in your answer. Which function should you use? Pause and choose. The answer is C, rank. Row number would give one of them number two, and drop it.
Summary
Top N per group, with one or two CTEs in front. You've got it. Two more challenges.
Key takeaways- ROW_NUMBER per group
- Two CTEs in a row
- Keep rank 1