Date filtering on a TEXT date column
March close — give me total OpEx spend for March 2025 across all cost centers. Join `dim_accounts`, filter to OpEx accounts, posted entries only. Single number, column `total_march_opex`.
| 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 |
Each hint you reveal reduces the XP you can earn. Try the query first.
You need to isolate OpEx transactions — that classification lives in the accounts dimension, not the transaction table itself. You'll also need to filter to a specific month and year.
Isolate a specific month and year from txn_date — EXTRACT(MONTH …) and EXTRACT(YEAR …) on a cast date will get you there.
One row, one column. Sum the posted amounts and give the column the alias Priya expects: total_march_opex.