Level 3 · Monthly revenue for 2025

“Revenue by month” may be the single most common analyst query. The trick is to turn each timestamp into a month key, then group by it.

DATE_TRUNC

DATE_TRUNC('month', ts) rounds a timestamp down to the start of its month: 2025-03-18 14:22:05 becomes 2025-03-01 00:00:00. Every order in March maps to the same value, so grouping by it gives one row per month.

SELECT
  CAST(DATE_TRUNC('month', order_date) AS DATE) AS month,
  COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;

Other units work the same way: 'day', 'week' (weeks start on Monday in Postgres and DuckDB), 'quarter', 'year'.

In DuckDB and Postgres, truncating a timestamp returns a timestamp. The CAST(... AS DATE) makes the result a clean date, which is nicer in charts and spreadsheets.

Why not EXTRACT(MONTH …)?

EXTRACT(MONTH FROM order_date) returns 1 to 12. Group by it over three years of data and March 2023, 2024 and 2025 are lumped into one row. DATE_TRUNC keeps the year, so it is the right key for a time series. Use EXTRACT (or DATE_PART) for seasonality questions like “which month of the year is busiest?”.

Dialects

database	month key
DuckDB, Postgres, Snowflake	DATE_TRUNC('month', order_date)
BigQuery	DATE_TRUNC(order_date, MONTH) (or TIMESTAMP_TRUNC for timestamps)
MySQL	DATE_FORMAT(order_date, '%Y-%m-01'), or DATE_SUB(DATE(order_date), INTERVAL DAY(order_date) - 1 DAY)
SQL Server	DATETRUNC(month, order_date) (2022+) or DATEFROMPARTS(YEAR(d), MONTH(d), 1)

Putting it together

Revenue needs the lines, status needs the order. After the join, the grain is order lines, so count orders with COUNT(DISTINCT o.order_id) (remember fan-out from chapter 5). Filter the year with a half-open range on the raw column, not on the truncated one, so the database can skip data efficiently.

Missing months

If a month had no completed orders, it would simply not appear. For a chart, a missing month is misleading: the line jumps straight from February to April. Generate a calendar and LEFT JOIN onto it:

SELECT CAST(m AS DATE) AS month
FROM generate_series(DATE '2025-01-01', DATE '2025-12-01', INTERVAL 1 MONTH) AS t(m);

generate_series exists in Postgres and DuckDB. BigQuery uses GENERATE_DATE_ARRAY. Harbor & Pine has sales every month, so you do not need it here, but in sparse data (one product, one small region) you will.

Common mistakes

• Grouping by the raw timestamp: one row per second, not per month.
• Formatting the month as text such as 'Mar 2025' and then sorting it: April sorts before January. Keep a real date for sorting and format at the very end, or in the chart tool.