Aggregate revenue by year and quarter using date extraction
Hey — board deck is due Friday and I need a clean revenue table: year, quarter, total revenue. Three years of data, 2023 through 2025. Pull this from fact_sales (grain: one row per sale — we want it aggregated up to year + quarter). The CEO wants to see the trend at a glance and will absolutely ask about the Q4 2025 number, so have an answer ready for whether it's a full quarter or partial.
| Column | Type | Key |
|---|---|---|
| sale_id | INT | PK |
| employee_id | INT | FK → dim_employees |
| product_id | INT | FK → dim_products |
| region_id | INT | FK → dim_regions |
| sale_date | TEXT | |
| quantity | INT | |
| revenue | REAL | |
| discount | REAL |
Each hint you reveal reduces the XP you can earn. Try the query first.
sale_date is stored as text, not a date type — you'll need to cast it before you can extract a year or quarter from it. Think about what PostgreSQL function extracts date parts.
Use EXTRACT(QUARTER FROM sale_date::date) for the quarter number (1–4)
GROUP BY both year and quarter, then ORDER BY year, quarter to make it readable