AI Hints
Homeโ€บSQL Tutorialโ€บPractice Gym: Windows and CTEs
SQL ยท Lesson 121 of 126

Practice Gym: Windows and CTEs

The power tools.

โฑ 2 min readโ–ถ Video at 2:52:46๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Practice Gym: Windows and CTEs
From the video: Practice Gym: Windows and CTEs
  • 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.

โœ๏ธ Exercise: First customer per country

The first customer to join in each country

Show hint

ROW_NUMBER() OVER (PARTITION BY country ORDER BY joined)

Show solution
solution.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  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.

โœ๏ธ Exercise: Best seller per genre

The best-selling book in each genre

Show hint

CTE for copies, then RANK() OVER (PARTITION BY genre ...)

Show solution
solution.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚   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.

Quick check
Keep BOTH tied best sellers

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
Next up: Gym: Trends and Sizes โ€” two final challenges