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
| id | month | amount |
|---|---|---|
| 1 | 2024-01 | 200 |
| 2 | 2024-02 | 350 |
| 3 | 2024-03 | 300 |
| 4 | 2024-04 | 450 |
Expected output (columns: month, amount, prev_amount)
2024-01|200|0
2024-02|350|200
2024-03|300|350
2024-04|450|300Hint —
LAG(amount, 1, 0) OVER (ORDER BY month)returns the previousrow'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"