Beginner
Case 16 of 50
Case 016: Revenue by Month
orderspayments
Leadership wants a trend line for the board meeting, and the manager brings the request to Alex.
"Rather than one number, could you show me revenue month by month?"
The data Alex is looking at
orders
| id | customer_id | order_date |
|---|---|---|
| 101 | 1 | 2026-01-05 |
| 102 | 2 | 2026-01-08 |
| 103 | 3 | 2026-01-12 |
| 104 | 1 | 2026-01-20 |
| 105 | 4 | 2026-02-01 |
| … 23 more rows | ||
payments
| id | order_id | amount |
|---|---|---|
| 601 | 101 | 6200 |
| 602 | 102 | 3500 |
| 603 | 103 | 3600 |
| 604 | 104 | 60000 |
| 605 | 105 | 12000 |
| … 23 more rows | ||
TASK
Help Alex answer the manager:
“Show total revenue for each calendar month.”
Write a query above and hit Run to see what comes back.
No hints requested yet — click "Get a hint" when you're stuck.
Alex's final query — this is for reference. Copy it into your own thinking, not into the editor above.
SELECT
DATE_FORMAT(orders.order_date, '%Y-%m') AS month,
SUM(payments.amount) AS revenue
FROM orders
JOIN payments ON payments.order_id = orders.id
GROUP BY month
ORDER BY month;
What that query returns
Case Debrief
Date functions, grouping by time period
- DATE_FORMAT() (a date-formatting function) lets you group timestamps by year, month, day, or any pattern you need.
- Quick tip: DATE_FORMAT() itself is MySQL-specific — here's the same "year-month" formatting in other major databases.
- Postgres / Snowflake: TO_CHAR(order_date, 'YYYY-MM')
- SQL Server: FORMAT(order_date, 'yyyy-MM')