Analytics Patterns · Self-join on period · YoY %
50. Year-over-year growth by quarter
Concept: Self-join on period · YoY %show
Year-over-year compares a period with the same period a year earlier, which cancels out seasonality. LAG(x, 4) over quarters only works if no quarter is ever missing; joining each row to the row with year - 1 and the same quarter is safe even when there are gaps.
Example (different data):
SELECT cur.year, cur.week, cur.signups, prev.signups AS last_year FROM weekly_signups cur LEFT JOIN weekly_signups prev ON prev.year = cur.year - 1 AND prev.week = cur.week;
Your task
The board deck compares every 2024 quarter with the same quarter of 2023, because Q4 is always the holiday spike.
For each region's 2024 quarters, return region, quarter, revenue, prev_year_revenue (same region and quarter in 2023, NULL if none) and yoy_pct (percent change from prev_year_revenue, rounded to 1 decimal, NULL if no prior year). Sort by region, quarter.
Starting Postgres in your browser…
Checking your access…