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.
Each hint you reveal reduces the XP you can earn. Try the query first.
Filter to `LOWER(account_type) IN ('revenue', 'cogs')`.
Use CASE: `SUM(CASE WHEN LOWER(account_type) = 'revenue' THEN amount ELSE 0 END) AS revenue`, same pattern for cogs.
Group by year and quarter: `EXTRACT(YEAR FROM txn_date::date)`, `EXTRACT(QUARTER FROM txn_date::date)`. 3 years × 4 quarters = 12 rows.