SQL ยท Lesson 113 of 126
Growth and Stars
What is driving the growth?.
The bank wants to know where the growth came from. Is it one genre, or everything? And which books should Helen stock more of?

- 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