Day-over-Day Sales Change - Medium SQL Window Functions Interview Problem

Problem

The business wants to see how sales are trending day over day. Using the sales table, return sale_date, amount, prev_amount (the previous day's amount), and change (amount minus prev_amount), ordered by date. The first row should have NULL for prev_amount and change.

Schema

sales

column	type
id	INTEGER
sale_date	DATE
amount	NUMERIC

Sample Data

id	sale_date	amount
1	2024-01-01	100
2	2024-01-02	150
3	2024-01-03	120
4	2024-01-04	200

Expected Output

sale_date	amount	prev_amount	change
2024-01-01	100	NULL	NULL
2024-01-02	150	100	50
2024-01-03	120	150	-30
2024-01-04	200	120	80

Hints