Combine LAG with PARTITION BY for YoY comparison within partitioned groups
I need year-over-year quarterly revenue comparison. For each quarter (Q1 through Q4), show the year, quarter, quarterly revenue, prior year's same-quarter revenue, and the YoY growth percentage. Q1 2023 should show NULL for prior year since we have no 2022 data. This is the single most important chart for the board.
Each hint you reveal reduces the XP you can earn. Try the query first.
Aggregate revenue by year and quarter in a subquery first
Use LAG(quarterly_revenue, 1) OVER (PARTITION BY quarter ORDER BY year) — PARTITION BY quarter ensures you compare Q1 to Q1, Q2 to Q2, etc.
YoY growth = (current - prior) / prior * 100