SUM with WHERE filter across a JOIN
Quick one for the cash forecast — total SaaS vendor spend YTD 2025. Join `fact_transactions` to `dim_vendors` on vendor_id where category is 'SaaS'. Only posted entries. One number, call the column `total_saas_spend`.
| 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 |
|---|---|---|
| vendor_id | INT | PK |
| vendor_name | TEXT | |
| category | TEXT | |
| country | TEXT | |
| onboarded_date | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Spend is in the transaction table but the vendor category (SaaS) is in a separate vendor table — connect them to filter by category. Watch out: check that the join key column names actually match on both sides.
Three filters apply simultaneously: vendor category, transaction status, and the calendar year. The year filter needs a cast since txn_date is stored as text.
Wrap everything in a SUM — the result should be a single scalar. The column alias total_saas_spend keeps the cash-forecast cell happy.