551. Order vs Previous Order
HardWindow FunctionsLAG
For every order return id, customer_id, amount and the same customer's previous order amount as prev_amount (NULL for their first), ordered by customer_id then order_date.
customers
| id | name | city |
|---|---|---|
| 1 | Anil | Delhi |
| 2 | Bina | Chennai |
| 3 | Chetan | Delhi |
| 4 | Divya | Kolkata |
| 5 | Esha | Chennai |
orders
| id | customer_id | amount | order_date |
|---|---|---|---|
| 1 | 1 | 500 | 2024-01-05 |
| 2 | 1 | 700 | 2024-02-10 |
| 3 | 2 | 200 | 2024-01-20 |
| 4 | 3 | 1500 | 2024-03-01 |
| 5 | 2 | 800 | 2024-03-15 |
| 6 | 1 | 300 | 2024-03-20 |
| 7 | 5 | 1000 | 2024-02-01 |
Your query runs on a fresh copy of these tables (SQLite). Column names in your result do not need to match.
Loading the MyLoginn IDE…