Best Sellers of the Month
Finishing the report.
Every report needs a highlight. Which books sold the most copies this month? Helen will put them in the shop window.

- Every report needs a highlight
- Top books this month
- For the shop window
Step 4: best sellers
Join the three tables, keep only December twenty twenty five, and only real sales. Then group by book, and sum the copies. Run it. Dragons of Ember Bay sold thirteen copies in December. Code of the River sold ten.
SELECT b.title,
SUM(oi.quantity) AS copies
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 strftime('%Y-%m', o.order_date) = '2025-12'
AND o.status IN ('delivered', 'shipped')
GROUP BY b.book_id
ORDER BY copies DESC
LIMIT 3;โโโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโโ โ title โ copies โ โโโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโโค โ Dragons of Ember Bay โ 13 โ โ Code of the River โ 10 โ โ Letters to Lisbon โ 8 โ โโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโโ
Active customers
One more number for the report. How many different customers placed an order in December twenty twenty five? Pause and try. Here's a solution. Count distinct customer ID, for orders in that month. Ninety two different customers.
Number of different customers who ordered in Dec 2025
Show hint
COUNT(DISTINCT customer_id) with a month filter
Show solution
SELECT COUNT(DISTINCT customer_id) AS customers_in_december
FROM orders
WHERE strftime('%Y-%m', order_date) = '2025-12';โโโโโโโโโโโโโโโโโโโโโโโโโ โ customers_in_december โ โโโโโโโโโโโโโโโโโโโโโโโโโค โ 92 โ โโโโโโโโโโโโโโโโโโโโโโโโโ
Sanity check
Before Helen trusts a report, check it against a number you already know. Earlier, with a completely different query, we found total revenue of forty four thousand two hundred and ninety eight dollars. Let's add up every order total the new way. Run it. Exactly the same. When two different methods agree, you can trust your numbers.
WITH order_totals AS (
SELECT o.order_id, o.status,
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 ROUND(SUM(total), 2) AS all_time_revenue
FROM order_totals
WHERE status IN ('delivered', 'shipped');โโโโโโโโโโโโโโโโโโโโ โ all_time_revenue โ โโโโโโโโโโโโโโโโโโโโค โ 44298.74 โ โโโโโโโโโโโโโโโโโโโโ
What you built
Look at what you've built. Order totals, monthly orders, cancellations, revenue, percentage change, best sellers, and active customers. Next month, Helen just changes the date and runs it again. No more spreadsheets. This is exactly the kind of work data analysts do every week.
Summary
You built a complete monthly report in SQL. Next project, a report about customers.
Key takeaways- CTEs step by step
- Window functions for trends
- Reusable every month