Final Project: The Year in Review
Everything together.
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.

- 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.
- 1headline numbers
- 2fastest growing genres
- 3star books
- 4what's going wrong?

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.
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;โโโโโโโโฌโโโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโโฌโโโโโโโโโโโโโ โ 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.
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