Lessons › SQL › Go further in SQL: Business questions

SQL lesson

Show every month's revenue, with 0 for empty months

Show every month from January to April 2026, with 0 for months we sold nothing. GROUP BY only shows months that have orders, so January and April would be missing. New tool: GENERATE_SERIES makes a list of dates, one per month. Start from that list and LEFT JOIN the totals onto it, so every month stays.

Example

A list of all the months, then a LEFT JOIN

WITH months AS (
  SELECT GENERATE_SERIES(DATE '2026-01-01', DATE '2026-04-01', INTERVAL '1 month')::date AS month
), totals AS (
  SELECT DATE_TRUNC('month', order_date)::date AS month,
         SUM(revenue) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT m.month, COALESCE(t.revenue, 0) AS revenue
FROM months m
LEFT JOIN totals t ON t.month = m.month
ORDER BY m.month

Result:

Step by step

1. Empty months show 0.

   WITH months AS (SELECT GENERATE_SERIES(DATE '2026-01-01', DATE '2026-04-01', INTERVAL '1 month')::date AS month), totals AS (SELECT DATE_TRUNC('month', order_date)::date AS month, SUM(revenue) AS revenue FROM orders GROUP BY DATE_TRUNC('month', order_date)) SELECT m.month, COALESCE(t.revenue, 0) AS revenue FROM months m LEFT JOIN totals t ON t.month = m.month ORDER BY m.month

2. GENERATE_SERIES(start, end, INTERVAL '1 month').

   WITH months AS (SELECT GENERATE_SERIES(DATE '2026-01-01', DATE '2026-04-01', INTERVAL '1 month')::date AS month), totals AS (SELECT DATE_TRUNC('month', order_date)::date AS month, SUM(revenue) AS revenue FROM orders GROUP BY DATE_TRUNC('month', order_date)) SELECT m.month, COALESCE(t.revenue, 0) AS revenue FROM months m LEFT JOIN totals t ON t.month = m.month ORDER BY m.month

3. That's why the list of months comes first.

   WITH months AS (SELECT GENERATE_SERIES(DATE '2026-01-01', DATE '2026-04-01', INTERVAL '1 month')::date AS month), totals AS (SELECT DATE_TRUNC('month', order_date)::date AS month, SUM(revenue) AS revenue FROM orders GROUP BY DATE_TRUNC('month', order_date)) SELECT m.month, COALESCE(t.revenue, 0) AS revenue FROM months m LEFT JOIN totals t ON t.month = m.month ORDER BY m.month

4. Two CTEs, then months LEFT JOIN totals, and COALESCE for the 0.

   WITH months AS (SELECT GENERATE_SERIES(DATE '2026-01-01', DATE '2026-04-01', INTERVAL '1 month')::date AS month), totals AS (SELECT DATE_TRUNC('month', order_date)::date AS month, SUM(revenue) AS revenue FROM orders GROUP BY DATE_TRUNC('month', order_date)) SELECT m.month, COALESCE(t.revenue, 0) AS revenue FROM months m LEFT JOIN totals t ON t.month = m.month ORDER BY m.month

Practice

Try each one in your head first, then open the answer.

WITH months AS (SELECT GENERATE_SERIES(DATE '2026-01-01', DATE '2026-04-01', INTERVAL '1 month')::date AS month), totals AS (SELECT DATE_TRUNC('month', order_date)::date AS month, SUM(revenue) AS revenue FROM orders GROUP BY DATE_TRUNC('month', order_date)) SELECT m.month, COALESCE(t.revenue, 0) AS revenue FROM months m LEFT JOIN totals t ON t.month = m.month ORDER BY m.month

WITH months AS (SELECT GENERATE_SERIES(DATE '2026-01-01', DATE '2026-04-01', INTERVAL '1 month')::date AS month), counts AS (SELECT DATE_TRUNC('month', order_date)::date AS month, COUNT(*) AS orders FROM orders GROUP BY DATE_TRUNC('month', order_date)) SELECT m.month, COALESCE(c.orders, 0) AS orders FROM months m LEFT JOIN counts c ON c.month = m.month ORDER BY m.month

SELECT GENERATE_SERIES(DATE '2026-01-01', DATE '2026-12-01', INTERVAL '1 month')::date AS month

WITH days AS (SELECT GENERATE_SERIES(DATE '2026-03-01', DATE '2026-03-07', INTERVAL '1 day')::date AS day), totals AS (SELECT order_date AS day, SUM(revenue) AS revenue FROM orders GROUP BY order_date) SELECT d.day, COALESCE(t.revenue, 0) AS revenue FROM days d LEFT JOIN totals t ON t.day = d.day ORDER BY d.day

WITH months AS (SELECT GENERATE_SERIES(DATE '2026-01-01', DATE '2026-04-01', INTERVAL '1 month')::date AS month), totals AS (SELECT DATE_TRUNC('month', order_date)::date AS month, SUM(revenue) AS revenue FROM orders WHERE store = 'Lagos' GROUP BY DATE_TRUNC('month', order_date)) SELECT m.month, COALESCE(t.revenue, 0) AS revenue FROM months m LEFT JOIN totals t ON t.month = m.month ORDER BY m.month