Write a query to compute year-over-year revenue comparison for each region using self-joins.

Answer

Self-join each region's current-year revenue to its own prior-year revenue by matching on region and an offset year.

Solution

SELECT curr.region, curr.year, curr.revenue AS curr_year_revenue,
       prev.revenue AS prev_year_revenue,
       ROUND(100.0 * (curr.revenue - prev.revenue) / prev.revenue, 2) AS yoy_growth_pct
FROM (
  SELECT region, YEAR(sale_date) AS year, SUM(amount) AS revenue
  FROM Sales GROUP BY region, YEAR(sale_date)
) curr
JOIN (
  SELECT region, YEAR(sale_date) AS year, SUM(amount) AS revenue
  FROM Sales GROUP BY region, YEAR(sale_date)
) prev
  ON curr.region = prev.region AND curr.year = prev.year + 1;

Step by Step

1. Both curr and prev are the same aggregated subquery: total revenue per region per year.
2. The self-join condition curr.year = prev.year + 1 pairs each year's row with the row from exactly one year earlier, for the same region.
3. Growth percentage is (current - previous) / previous, exactly as in the earlier month-over-month growth question, just applied at a yearly grain.

Alternative Method: LAG() Window Function

WITH yearly AS (
  SELECT region, YEAR(sale_date) AS year, SUM(amount) AS revenue
  FROM Sales GROUP BY region, YEAR(sale_date)
)
SELECT region, year, revenue,
       LAG(revenue) OVER (PARTITION BY region ORDER BY year) AS prev_year_revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY year))
             / LAG(revenue) OVER (PARTITION BY region ORDER BY year), 2) AS yoy_growth_pct
FROM yearly;

LAG() is generally the more idiomatic modern approach - it avoids the self-join entirely and handles any gaps in years more gracefully (a self-join on year + 1 would simply produce no match for a missing year, while LAG() would compare against whatever the actual previous available year happens to be, which can be a subtle but important difference).

Key Exam Traps

• Self-join on year + 1 assumes no missing years - if a region has a gap year with zero sales, the self-join simply produces no row for that comparison, while LAG() would incorrectly compare against a year that isn't actually adjacent. Both approaches need this edge case flagged explicitly.
• First year for any region has no valid prior-year match - the self-join naturally excludes it (no matching row), and the LAG() version returns NULL for it - both are correct, just expressed differently.
• Region renames or restructuring over time (e.g. a region split into two) would break the year-over-year comparison silently, since past and current data would no longer share the same region key - a real-world data quality concern worth mentioning.