Month over Month in Python

A classic interview question: for each month, show the revenue and the change from the month before, in percent. The rows of a SELECT can't see each other, so the trick is to bring last month's revenue onto this month's row. LAG does that: LAG(x) OVER (ORDER BY …) lines the rows up in that order and reads x from the row just before. The first row has no row before, so it gets NULL.

Example: on a table of daily visits, this puts the day before's visits next to each day's:

SELECT day, visits, LAG(visits) OVER (ORDER BY day) AS day_before
FROM daily_visits

The change in percent is the difference divided by the old value, times 100. Write 100.0, not 100: revenue is a whole number, and in SQLite a whole number divided by a whole number drops its decimals (500 / 63 is 7). ROUND(…, 1) keeps one decimal. On the first month the old value is NULL, and arithmetic with NULL gives NULL, so that row needs no extra code.

The monthly named query is already in your editor: revenue per month for the orders that weren't cancelled. The hidden setup adds two June orders:

order 8: customer 5, 2026-06-04, paid   items: 2 × 15 + 1 × 22 = 52
order 9: customer 1, 2026-06-20, paid   items: 3 × 12 + 4 × 2 = 44

Your query must print:

month | revenue | previous | pct_change
2026-03 | 63 | NULL | NULL
2026-04 | 68 | 63 | 7.9
2026-05 | 135 | 68 | 98.5
2026-06 | 96 | 135 | -28.9
(4 rows)

Your task: below monthly, add a second named query, with_previous, that selects each month, its revenue and the month before's revenue with LAG, named previous. Then select from with_previous the month, revenue, previous and the change in percent rounded to 1 decimal, named pct_change.

Assignment
Follow the Coding Tutorial to add the with_previous named query and the main query that computes pct_change, one clause per line.

Hint
Name LAG's result previous inside with_previous, so the main query can use it like any other column. Multiply the difference by 100.0 before dividing by previous.