Multi-table JOIN with GROUP BY on two dimensions
Building the 2025 monthly spend trend by department. OpEx only, posted only, all 11 months we have so far. Columns: `department`, `month`, `total_spend`. Sort by department then month.
| Column | Type | Key |
|---|---|---|
| txn_id | INT | PK |
| txn_date | TEXT | |
| account_id | INT | FK → dim_accounts |
| cost_center_id | INT | FK → dim_cost_centers |
| vendor_id | INT | FK → dim_vendors |
| amount | REAL | |
| currency | TEXT | |
| description | TEXT | |
| posted | INT | |
| posted_date | TEXT |
| Column | Type | Key |
|---|---|---|
| account_id | INT | PK |
| account_code | TEXT | |
| account_name | TEXT | |
| account_type | TEXT | |
| is_active | INT |
| Column | Type | Key |
|---|---|---|
| cost_center_id | INT | PK |
| cost_center_code | TEXT | |
| cost_center_name | TEXT | |
| department | TEXT | |
| owner_email | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Spending amount, account type, and department name all live in different tables — you'll need to bring them together. Once joined, think about how to group by both department and month simultaneously.
Filter `LOWER(account_type) = 'opex'` — there's no casing trap here since the miscase is on Revenue, but the habit matters.
GROUP BY department, month — use `EXTRACT(MONTH FROM txn_date::date)` for the month number.