codejump
HardPro challengeSQL

Month-over-Month Revenue Change

SQLDatabases

Write a SQL query that shows, for each month, the revenue **and the previous
month's revenue** side by side. Use 0 as the default when there is no
previous month. Order by month.

Table: `revenue`

idmonthamount
12024-01200
22024-02350
32024-03300
42024-04450

Expected output (columns: month, amount, prev_amount)

2024-01|200|0
2024-02|350|200
2024-03|300|350
2024-04|450|300

Hint — LAG(amount, 1, 0) OVER (ORDER BY month) returns the previous

row's amount, defaulting to 0 if none exists.

Sample tests

Test #1Jan has no previous month → 0; others carry previous amount
Input: "CREATE TABLE revenue (id INTEGER, month TEXT, amount INTEGER);\nINSERT INTO revenue VALUES\n (1, '2024-01', 200),\n (2, '2024-02', 350),\n (3, '2024-03', 300),\n (4, '2024-04', 450);"
Output: "2024-01|200|0\n2024-02|350|200\n2024-03|300|350\n2024-04|450|300"
← All challenges