SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…