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

Monthly Numbers

Orders, cancellations, revenue.

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

Now we group the order totals by month. We want three numbers per month. Orders, cancellations, and revenue. And only real revenue, so cancelled and returned orders don't count.

Monthly Numbers
From the video: Monthly Numbers
  • Group by month
  • Three numbers
  • Only real revenue

Step 2: group by month

The CTE stays the same. Below it, we group by month. Count star gives the orders. Case inside sum counts the cancellations. And for revenue, case keeps the total only for delivered and shipped orders, so the rest are ignored. Run it. August had the most revenue, at about three thousand four hundred dollars. December had sixteen cancellations, the most of any month.

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 strftime('%Y-%m', order_date) AS month,
       COUNT(*) AS orders,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
       ROUND(SUM(CASE WHEN status IN ('delivered', 'shipped')
                      THEN total END), 2) AS revenue
FROM order_totals
WHERE order_date >= '2025-07-01'
GROUP BY month;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  month  โ”‚ orders โ”‚ cancelled โ”‚ revenue โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 2025-07 โ”‚ 77     โ”‚ 11        โ”‚ 2196.23 โ”‚
โ”‚ 2025-08 โ”‚ 120    โ”‚ 8         โ”‚ 3423.57 โ”‚
โ”‚ 2025-09 โ”‚ 93     โ”‚ 9         โ”‚ 2593.02 โ”‚
โ”‚ 2025-10 โ”‚ 93     โ”‚ 7         โ”‚ 2638.96 โ”‚
โ”‚ 2025-11 โ”‚ 83     โ”‚ 6         โ”‚ 2523.04 โ”‚
โ”‚ 2025-12 โ”‚ 104    โ”‚ 16        โ”‚ 2569.5  โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Step 3: compare months

Now we add two more CTEs. Monthly works out revenue per month. Changes uses lag to bring in last month's revenue, next to this month's. Then the percentage change is, this month minus last month, divided by last month, times one hundred. We filter at the very end, so July can still see June. Run it. August revenue jumped by about fifty six percent. September fell back by about twenty four percent.

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
),
monthly AS (
    SELECT strftime('%Y-%m', order_date) AS month,
           SUM(CASE WHEN status IN ('delivered', 'shipped')
                    THEN total END) AS revenue
    FROM order_totals
    GROUP BY month
),
changes AS (
    SELECT month, revenue,
           LAG(revenue) OVER (ORDER BY month) AS last_month
    FROM monthly
)
SELECT month,
       ROUND(revenue, 0) AS revenue,
       ROUND(100.0 * (revenue - last_month) / last_month, 1) AS pct_change
FROM changes
WHERE month >= '2025-07';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  month  โ”‚ revenue โ”‚ pct_change โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 2025-07 โ”‚ 2196.0  โ”‚ -12.0      โ”‚
โ”‚ 2025-08 โ”‚ 3424.0  โ”‚ 55.9       โ”‚
โ”‚ 2025-09 โ”‚ 2593.0  โ”‚ -24.3      โ”‚
โ”‚ 2025-10 โ”‚ 2639.0  โ”‚ 1.8        โ”‚
โ”‚ 2025-11 โ”‚ 2523.0  โ”‚ -4.4       โ”‚
โ”‚ 2025-12 โ”‚ 2570.0  โ”‚ 1.8        โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Group by month gives the numbers, and lag gives the change. That's the heart of the monthly report. Last step, the best sellers.

Key takeaways
  • GROUP BY month
  • CASE for cancellations
  • LAG for % change
Next up: Best Sellers of the Month โ€” finishing the report