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

Project 1: The Monthly Report

A report Helen can use.

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

Time for a real project. Every month, Helen wants a short report. How many orders came in, how many were cancelled, how much money came in, and how that compares with the month before. Plus the three best sellers. Right now, she does it by hand in a spreadsheet. Let's build it in SQL, one step at a time.

Project 1: The Monthly Report
From the video: Project 1: The Monthly Report
  • A monthly report
  • Orders, cancellations, revenue
  • Plus the best sellers

The plan

Here's the plan. Step one, work out each order's total. Step two, group those totals by month. Step three, compare each month with the one before. And step four, find the best sellers. Each step builds on the last.

  1. Step 1order totals
  2. Step 2group by month
  3. Step 3compare months
  4. Step 4best sellers
The plan
From the video: The plan

Step 1: order totals

Step one is a CTE you've seen before. Join orders, order items, and books, and sum quantity times price for each order. Then we look at the newest few orders, to check it works. Run it. Each order now has a total. Always check each step before building the next one.

query.sqlsql
WITH order_totals AS (
    SELECT o.order_id, o.order_date, 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 order_id, order_date, status, ROUND(total, 2) AS total
FROM order_totals
ORDER BY order_date DESC
LIMIT 4;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ order_id โ”‚ order_date โ”‚  status   โ”‚ total โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 1455     โ”‚ 2025-12-31 โ”‚ delivered โ”‚ 35.98 โ”‚
โ”‚ 1713     โ”‚ 2025-12-31 โ”‚ cancelled โ”‚ 11.49 โ”‚
โ”‚ 1963     โ”‚ 2025-12-31 โ”‚ cancelled โ”‚ 14.99 โ”‚
โ”‚ 2414     โ”‚ 2025-12-31 โ”‚ delivered โ”‚ 29.99 โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Why build it step by step?

Why not write the whole report in one go? Because long queries are hard to check. If the final numbers look wrong, you won't know where the mistake is. Building in small steps, and checking each one, is slower for five minutes, and much faster for the rest of the day.

Big queryhard to check
Small stepseasy to check
Check each onebefore the next
Resultnumbers you trust

Summary

Step one gives every order a total. Next, turn those totals into monthly numbers.

Key takeaways
  • CTE: order_totals
  • quantity * price
  • Check each step
Next up: Monthly Numbers โ€” orders, cancellations, revenue