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

Final Project: The Year in Review

Everything together.

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

It's January, and Helen has a meeting with her bank. She wants to expand the shop, and the bank wants numbers. How did the business do this year, compared with last year? What's growing? What's going wrong? You're her analyst. Let's answer the bank's questions, using everything you've learned.

Final Project: The Year in Review
From the video: Final Project: The Year in Review
  • A meeting with the bank
  • How did the year go?
  • You're the analyst

The bank's questions

The bank has four questions. One, the headline numbers for each year. Two, which genres are growing fastest. Three, which books are the stars. And four, is anything going wrong. Let's take them one at a time.

  1. 1headline numbers
  2. 2fastest growing genres
  3. 3star books
  4. 4what's going wrong?
The bank's questions
From the video: The bank's questions

Question 1: the headline numbers

Question one. ACTE gives every order a total. Then, per year, we count orders and distinct customers, add up real revenue, and work out the cancellation rate. Run it. Orders almost doubled, from five hundred and fifty nine to one thousand and thirty nine. Customers grew from two hundred and seventy one to four hundred and fifteen. Revenue went from fifteen thousand seven hundred, to twenty eight thousand six hundred. But the cancellation rate rose, from seven point two percent, to nine point six.

query.sqlsql
WITH order_totals AS (
    SELECT o.order_id, o.order_date, o.status, o.customer_id,
           SUM(oi.quantity * b.price) AS total
    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
    GROUP BY o.order_id
)
SELECT strftime('%Y', order_date)       AS year,
       COUNT(*)                         AS orders,
       COUNT(DISTINCT customer_id)      AS customers,
       ROUND(SUM(CASE WHEN status IN ('delivered', 'shipped')
                      THEN total END), 0) AS revenue,
       ROUND(100.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)
             / COUNT(*), 1)             AS cancel_pct
FROM order_totals
GROUP BY year;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ year โ”‚ orders โ”‚ customers โ”‚ revenue โ”‚ cancel_pct โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 2024 โ”‚ 559    โ”‚ 271       โ”‚ 15694.0 โ”‚ 7.2        โ”‚
โ”‚ 2025 โ”‚ 1039   โ”‚ 415       โ”‚ 28605.0 โ”‚ 9.6        โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Reading the headline

Let's read these numbers like an analyst. Orders grew by eighty six percent. Revenue grew by eighty two percent. So the average order stayed about the same, around thirty two dollars. The growth came from more customers, not bigger baskets. That's a useful insight. It tells Helen that her marketing is working, and the next step could be getting each customer to buy a little more.

Orders+86%
Revenue+82%
Average orderabout the same
Growth frommore customers

Summary

The headline numbers look strong, with one warning sign. Next, what's driving the growth.

Key takeaways
  • Orders nearly doubled
  • Revenue +82%
  • Cancellations up
Next up: Growth and Stars โ€” what is driving the growth?