First multi-key GROUP BY with date manipulation. Practice substring-based month extraction (substr(text, 1, 7)), grouping by two columns, and producing a long-format time series the BI team can pivot into a chart.
Monthly seller report. Pull orders by (month, marketplace_name) for the last 12 months (order_ts >= '2025-04-01'). Three columns: month (substr(order_ts, 1, 7) = YYYY-MM), marketplace_name, orders (COUNT). Sort month ASC, marketplace_name ASC. The chart team pivots this in Looker — long format is what they want.
| Column | Type | Key |
|---|---|---|
| order_id | INT | PK |
| listing_id | INT | FK → dim_listings |
| marketplace_id | INT | FK → dim_marketplaces |
| order_ts | TEXT | |
| units | INT | |
| gross_amount | REAL | |
| refunded | INT |
| Column | Type | Key |
|---|---|---|
| marketplace_id | INT | PK |
| marketplace_name | TEXT | |
| marketplace_fee_pct | REAL | |
| fba_supported | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
substr(order_ts, 1, 7) gives 'YYYY-MM' from a TEXT order_ts of 'YYYY-MM-DD HH:MM:SS'.
JOIN dim_marketplaces for the human-readable name.
Filter order_ts >= '2025-04-01'.
GROUP BY substr(order_ts, 1, 7), marketplace_name. Both keys needed.