Truncate a date column to month-grain with `substr(date, 1, 7)` (SQLite-portable) and group by it. The fundamental time-series rollup that drives every monthly bookings dashboard.
Monthly cadence dashboard. Truncate `created_date` to year-month (YYYY-MM) and count orders per month. Use `ecom_orders`. One row per month, ordered chronologically (ascending). I want to see the holiday spike in Dec and the April push we just did.
| Column | Type | Key |
|---|---|---|
| order_id | INT | PK |
| customer_id | INT | FK → ecom_customers |
| order_number | TEXT | |
| financial_status | TEXT | |
| fulfillment_status | TEXT | |
| total_price | REAL | |
| subtotal | REAL | |
| tax | REAL | |
| shipping | REAL | |
| discount_total | REAL | |
| channel | TEXT | |
| created_date | TEXT | |
| processed_date | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
SQLite-portable month truncation: `substr(created_date, 1, 7)` returns 'YYYY-MM' from a 'YYYY-MM-DD ...' string.
GROUP BY the truncated month column, COUNT(*) for the orders. ORDER BY month ASC for chronological output.
You should see 15 monthly buckets. April 2025 is the biggest at 37 orders (the demo seed concentrates the recent push).