The everyday reporting query: count/sum per month.
Count per month
-- PostgreSQL / DuckDB / Snowflake
SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*)
FROM events
GROUP BY 1
ORDER BY 1;
-- BigQuery
SELECT DATE_TRUNC(created_at, MONTH) AS month, COUNT(*)
FROM events
GROUP BY month
ORDER BY month;
-- MySQL (no DATE_TRUNC — bucket with DATE_FORMAT)
SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS month, COUNT(*)
FROM events
GROUP BY month
ORDER BY month;
-- SQLite
SELECT strftime('%Y-%m', created_at) AS month, COUNT(*)
FROM events
GROUP BY month
ORDER BY month;
Per week instead
SELECT DATE_TRUNC('week', created_at) AS week, COUNT(*) ... -- Postgres / DuckDB / Snowflake
SELECT DATE_TRUNC(created_at, WEEK(MONDAY)) AS week ... -- BigQuery
SELECT strftime('%Y-%W', created_at) AS week ... -- SQLite (approx)
Gotchas: GROUP BY 1 (group by the first select item) works in Postgres, DuckDB, Snowflake and
BigQuery but not MySQL/SQLite in all modes — repeat the expression or use its alias there.
Truncating with DATE_TRUNC keeps a real date (sortable, joinable to a calendar); DATE_FORMAT/
strftime give a text label that sorts correctly only in YYYY-MM form. For zero-filled months,
left-join this against a generated date series (see that recipe).