Year-over-Year Game Sales Growth

A small board-game publisher wants to see how each title's sales move from one calendar year to the next.

For every game and every year in which it sold at least once, return game, sales_year (an integer such as 2024), total_sales (sum of amount that year) and growth_pct: the percentage change against the same game's total in the immediately preceding calendar year, computed as (this year - last year) * 100 / last year and rounded to 2 decimals.

growth_pct is NULL for a game's first year, and also when the game had no sales in the previous calendar year (a gap year is not compared across). Order by game, then sales_year. An empty table returns no rows.

Tables

game_sales — One row per wholesale order of a single game title.

Example

game_sales

Expected output

Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Window Functions · problem 41 of 70 · SQLite

The idea

Work in two layers. First aggregate the raw orders into yearly totals per game: strftime('%Y', sold_on) extracts the year and GROUP BY game, sales_year sums the amounts.

Then use LAG partitioned by game and ordered by year to bring the previous row's year and total onto the current row. A plain LAG is not enough on its own: if a game skipped 2023, the row before 2024 is 2022, and comparing those would overstate a one-year change. Checking prev_year = sales_year - 1 turns that case into NULL, exactly like the first year.

The percentage uses 100.0 so the division happens in floating point, and ROUND(..., 2) applies the requested precision.

The alternative self-joins the yearly totals to themselves on prev.sales_year = cur.sales_year - 1. The LEFT JOIN leaves missing previous years as NULL, which propagates through the arithmetic for free.

Solution

WITH yearly AS (
  SELECT game,
         CAST(strftime('%Y', sold_on) AS INTEGER) AS sales_year,
         SUM(amount) AS total_sales
  FROM game_sales
  GROUP BY game, sales_year
),
with_prev AS (
  SELECT game, sales_year, total_sales,
         LAG(sales_year)  OVER w AS prev_year,
         LAG(total_sales) OVER w AS prev_total
  FROM yearly
  WINDOW w AS (PARTITION BY game ORDER BY sales_year)
)
SELECT game,
       sales_year,
       total_sales,
       CASE WHEN prev_year = sales_year - 1
            THEN ROUND((total_sales - prev_total) * 100.0 / prev_total, 2)
       END AS growth_pct
FROM with_prev
ORDER BY game, sales_year;

Another way

WITH yearly AS (
  SELECT game,
         CAST(strftime('%Y', sold_on) AS INTEGER) AS sales_year,
         SUM(amount) AS total_sales
  FROM game_sales
  GROUP BY game, sales_year
)
SELECT cur.game,
       cur.sales_year,
       cur.total_sales,
       ROUND((cur.total_sales - prev.total_sales) * 100.0 / prev.total_sales, 2) AS growth_pct
FROM yearly AS cur
LEFT JOIN yearly AS prev
  ON prev.game = cur.game
 AND prev.sales_year = cur.sales_year - 1
ORDER BY cur.game, cur.sales_year;

Why common answers fail

LAG returns the previous row, not the previous calendar year, so after a gap year it compares against a year that is two or more years back.

WITH yearly AS (
  SELECT game, CAST(strftime('%Y', sold_on) AS INTEGER) AS sales_year, SUM(amount) AS total_sales
  FROM game_sales GROUP BY game, sales_year
)
SELECT game, sales_year, total_sales,
       ROUND((total_sales - LAG(total_sales) OVER (PARTITION BY game ORDER BY sales_year)) * 100.0
             / LAG(total_sales) OVER (PARTITION BY game ORDER BY sales_year), 2) AS growth_pct
FROM yearly
ORDER BY game, sales_year;

Multiplies by the integer 100 instead of 100.0, so integer division truncates the percentage (33.33 becomes 33).

WITH yearly AS (
  SELECT game, CAST(strftime('%Y', sold_on) AS INTEGER) AS sales_year, SUM(amount) AS total_sales
  FROM game_sales GROUP BY game, sales_year
), with_prev AS (
  SELECT *, LAG(sales_year) OVER w AS prev_year, LAG(total_sales) OVER w AS prev_total
  FROM yearly WINDOW w AS (PARTITION BY game ORDER BY sales_year)
)
SELECT game, sales_year, total_sales,
       CASE WHEN prev_year = sales_year - 1
            THEN ROUND((total_sales - prev_total) * 100 / prev_total, 2) END AS growth_pct
FROM with_prev
ORDER BY game, sales_year;

Leaves out PARTITION BY game, so the 'previous' row can belong to a different game.

WITH yearly AS (
  SELECT game, CAST(strftime('%Y', sold_on) AS INTEGER) AS sales_year, SUM(amount) AS total_sales
  FROM game_sales GROUP BY game, sales_year
), with_prev AS (
  SELECT *, LAG(sales_year) OVER w AS prev_year, LAG(total_sales) OVER w AS prev_total
  FROM yearly WINDOW w AS (ORDER BY sales_year)
)
SELECT game, sales_year, total_sales,
       CASE WHEN prev_year = sales_year - 1
            THEN ROUND((total_sales - prev_total) * 100.0 / prev_total, 2) END AS growth_pct
FROM with_prev
ORDER BY game, sales_year;

What the interviewer is checking

Tests whether you aggregate before applying a window function and whether you notice that "previous row" and "previous year" are different things when data has gaps.

Mistakes to avoid

• Applying LAG to raw orders instead of yearly totals.
• Comparing across a gap year because LAG simply takes the prior row.
• Integer division from multiplying by 100 instead of 100.0.
• Forgetting PARTITION BY, so one game is compared with another.

Follow-ups to expect

• Show month-over-month growth instead (group by strftime('%Y-%m') and compare with date(month, '-1 month')).
• Return only games whose sales grew every year (check that MIN(growth_pct) > 0 over non-NULL rows).
• Compare with the same year two years earlier (LAG(total_sales, 2) plus a year check, or a self-join on year - 2).

In real work

Finance and product dashboards show year-over-year and month-over-month growth everywhere, and gaps in history are a classic source of misleading percentages.

Practice next