CASE expressions + date bucketing for P&L-style aggregation
Board prep. Need gross margin by quarter across 2023-2025. Columns: `year`, `quarter`, `revenue`, `cogs`, `gross_margin` (revenue minus cogs). Use LOWER() on account_type — there's a known casing bug. Posted entries only.
| 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.
Gross margin requires two account types from the same table — but you want them as separate columns in one row per quarter, not as separate rows. Think about how to pivot account types into columns within an aggregation.
Use CASE: `SUM(CASE WHEN LOWER(account_type) = 'revenue' THEN amount ELSE 0 END) AS revenue`, same pattern for cogs.
Group by both year and quarter using EXTRACT on the cast date — one row per period covers your full data range.