Intermediate#10 · strftime, GROUP BY

Monthly revenue, 2026

Return revenue per month for 2026 as month (format YYYY-MM) and revenue rounded to 2 decimals, in calendar order. Leave out cancelled orders.

Write your query in the lab. It runs in your browser and tells you exactly what's wrong if it doesn't match.

Solve it in the lab

Hints

Hint 1

strftime('%Y-%m', order_date) turns a date into '2026-03'. substr(order_date, 1, 7) also works.

Hint 2

Filter to 2026 with order_date >= '2026-01-01'.

Expected output

Your result should match this, in this order (9 rows).

month	revenue
2026-01	5060.8
2026-02	3311.1
2026-03	2916.4
2026-04	1460.2
2026-05	1251.5
2026-06	7446.8
2026-07	9640.4
2026-08	1899.1
2026-09	8174.3

Model answer

Show the solution

SELECT strftime('%Y-%m', o.order_date) AS month,
       ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled' AND o.order_date >= '2026-01-01'
GROUP BY month
ORDER BY month;

Other queries that return the same rows are correct too. The lab checks your result, not your exact wording.

Next challenge: Loyal customers →