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 |