AI Hints
Homeโ€บSQL Tutorialโ€บProject: The Monthly Report
SQL ยท Lesson 83 of 126

Best Sellers of the Month

Finishing the report.

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

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

Best Sellers of the Month
From the video: Best Sellers of the Month
  • 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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚        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.

โœ๏ธ Exercise: Active customers

Number of different customers who ordered in Dec 2025

Show hint

COUNT(DISTINCT customer_id) with a month filter

Show solution
solution.sqlsql
SELECT COUNT(DISTINCT customer_id) AS customers_in_december
FROM orders
WHERE strftime('%Y-%m', order_date) = '2025-12';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

query.sqlsql
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');
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

Totalsper order
Monthlyorders, cancellations, revenue
Trend% change with LAG
Highlightsbest sellers, customers

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
Next up: Project 2: The Customer Report โ€” who buys, and who stopped