Solving tips
- Bucketing timestamps by period is a job for `date_trunc('month', ts)`, which normalizes every timestamp to the first instant of its month.
- Group and order by the truncated value, not the raw timestamp, so all rows in a month collapse together.
- Cast the bucket to `::date` for a clean YYYY-MM-DD label and to drop the time-of-day component.
Orders arrive with a full timestamp. Roll them up into total revenue per calendar month.
Schema
CREATE TABLE orders (
order_id INTEGER,
ordered_at TIMESTAMP,
amount INTEGER
);
Sample data:
| order_id | ordered_at | amount |
|---|
| 1 | 2024-01-05 09:12:00 | 100 |
| 2 | 2024-01-20 16:45:00 | 50 |
| 3 | 2024-02-10 11:00:00 | 200 |
| 4 | 2024-02-15 08:30:00 | 75 |
| 5 | 2024-03-01 00:05:00 | 300 |
| 6 | 2024-03-30 23:59:00 | 25 |
Task
Return one row per calendar month with columns month (the first day of the month, as a date) and revenue (the SUM of amount for orders in that month). Order by month ascending.
Expected output
| month | revenue |
|---|
| 2024-01-01 | 150 |
| 2024-02-01 | 275 |
| 2024-03-01 | 325 |
Approach
date_trunc('month', ordered_at) maps every timestamp to midnight on the first day of its month, so all orders in the same month share one bucket key. Grouping by that key and summing amount gives per-month revenue; casting the bucket to ::date produces the clean date label the task asks for.
Query
SELECT
date_trunc('month', ordered_at)::date AS month,
SUM(amount) AS revenue
FROM orders
GROUP BY date_trunc('month', ordered_at)
ORDER BY month;
Walkthrough
Orders 1 and 2 both truncate to 2024-01-01, so they group together for revenue 100 + 50 = 150. Orders 3 and 4 truncate to 2024-02-01, giving 200 + 75 = 275. Orders 5 and 6 truncate to 2024-03-01, giving 300 + 25 = 325. Note that the differing times of day (including 23:59:00 on order 6) are all flattened to the month start. ORDER BY month returns the three buckets chronologically.
Complexity & notes
One scan plus a grouping aggregate, O(n). You can GROUP BY 1 (the select-list position) or repeat the date_trunc expression; grouping by the raw ordered_at would defeat the bucketing and yield a row per order. date_trunc accepts other units ('day', 'week', 'quarter', 'year'), so the same shape rebuckets to any granularity. Dialect note: date_trunc is PostgreSQL; MySQL uses DATE_FORMAT(ordered_at, '%Y-%m-01') and SQL Server uses DATETRUNC (2022+) or DATEFROMPARTS(YEAR(...), MONTH(...), 1). With timestamptz, date_trunc truncates in the session time zone unless you pass an explicit zone as a third argument.