DATE_TRUNC for time-bucketed aggregation — the foundation of every period-over-period report
I'm building the quarterly bookings dashboard and need the deal count by close quarter. Truncate `close_date` to the start of its quarter, group by that, count opportunities. Return the quarter start date and the count. Order by quarter ascending. Include every stage — open, won, lost — the dashboard filters by stage downstream, but the data-layer query gives the full picture.
| Column | Type | Key |
|---|---|---|
| opportunity_id | INT | PK |
| account_id | INT | FK → revops_accounts |
| owner_user_id | INT | FK → revops_users |
| name | TEXT | |
| stage | TEXT | |
| amount | REAL | |
| close_date | TEXT | |
| created_date | TEXT | |
| is_closed | INT | |
| is_won | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Postgres/SQLite-portable: `DATE_TRUNC('quarter', close_date::date)` (casting TEXT to date first). SQLite alternative: `strftime('%Y-', close_date) || printf('%02d', ((CAST(strftime('%m', close_date) AS INTEGER) - 1) / 3) * 3 + 1) || '-01'` — hairy. The Postgres form is cleaner; our backend is Postgres (Supabase).
Group by the truncated expression. `COUNT(*)` as the metric.
You should see exactly 5 quarters — 2024-Q4 through 2025-Q4 — covering every close_date in the seed.