AI Hints
Homeโ€บSQL Tutorialโ€บFinal Project: The Year in Review
SQL ยท Lesson 113 of 126

Growth and Stars

What is driving the growth?.

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

The bank wants to know where the growth came from. Is it one genre, or everything? And which books should Helen stock more of?

Growth and Stars
From the video: Growth and Stars
  • Where did growth come from?
  • One genre, or all?
  • Which books to stock?

Question 2: fastest growing genres

ACTE pivots revenue into two columns, one per year. Then we work out the percentage growth for each genre. Run it. History grew the most, by one hundred and sixteen percent. Business and fantasy more than doubled too. Every single genre grew.

query.sqlsql
WITH by_year AS (
    SELECT b.genre,
           SUM(CASE WHEN o.order_date <  '2025-01-01'
                    THEN oi.quantity * b.price END) AS y2024,
           SUM(CASE WHEN o.order_date >= '2025-01-01'
                    THEN oi.quantity * b.price END) AS y2025
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    JOIN books       AS b  ON b.book_id   = oi.book_id
    WHERE o.status IN ('delivered', 'shipped')
    GROUP BY b.genre
)
SELECT genre,
       ROUND(100.0 * (y2025 - y2024) / y2024, 0) AS growth_pct
FROM by_year
ORDER BY growth_pct DESC
LIMIT 4;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  genre   โ”‚ growth_pct โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ History  โ”‚ 116.0      โ”‚
โ”‚ Business โ”‚ 112.0      โ”‚
โ”‚ Fantasy  โ”‚ 108.0      โ”‚
โ”‚ Thriller โ”‚ 90.0       โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Question 3: the star books

Now the stars. Copies sold in twenty twenty five, top three. Run it. Dragons of Ember Bay sold one hundred and thirty two copies. Helen should never let it go out of stock.

query.sqlsql
SELECT b.title,
       SUM(oi.quantity) AS copies_2025
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN books       AS b  ON b.book_id   = oi.book_id
WHERE o.order_date >= '2025-01-01'
  AND o.status IN ('delivered', 'shipped')
GROUP BY b.book_id
ORDER BY copies_2025 DESC
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚        title         โ”‚ copies_2025 โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Dragons of Ember Bay โ”‚ 132         โ”‚
โ”‚ Code of the River    โ”‚ 99          โ”‚
โ”‚ The Silent Orchard   โ”‚ 92          โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Growth came from every genre, led by history, business, and fantasy, and one book is a clear star. Last question. What's going wrong?

Key takeaways
  • History +116%
  • Every genre grew
  • Dragons of Ember Bay
Next up: What Is Going Wrong? โ€” finding the problem