SQL Quest › SQL Interview Questions › Window Functions
The Previous Order's Total (LAG)
Before you can compute growth, you need the number you're growing from. Ops wants every order listed next to the total of the order that came immediately before it.
Show order_id, order_date, total, and prev_total (the total of the previous order by date). Order by order_date, then order_id.
The new idea: LAG(total) OVER (ORDER BY order_date, order_id) reaches backwards one row in the ordered window. Until now your window functions summarised rows; this one reads a different row.
Expect the first row's prev_total to be NULL — there is no row before it. That NULL is not a bug, and handling it is exactly what separates a working month-over-month query from one that silently drops its first period.
Solve it in the browser editor →
Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".
Schema
orders
| order_id | customer_id | product | category | quantity | price | total | order_date | country | status |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | Laptop Pro | Electronics | 1 | 1299.99 | 1299.99 | 2024-01-15 | USA | completed |
| 2 | 2 | Wireless Mouse | Electronics | 2 | 49.99 | 99.98 | 2024-01-16 | Canada | completed |
| 3 | 3 | Office Chair | Furniture | 1 | 349.99 | 349.99 | 2024-01-17 | USA | completed |
Expected output: row 1 → prev_total NULL; row 2 → prev_total 1299.99
Hint
SELECT, Window Functions, LAG, and open the hint there if you stall.Concepts
SELECT Window Functions LAG Date Functions
Practise the topic: SQL practice questions · Window function practice · Date function practice
In these company practice sets
Anthropic · OpenAI · Stripe · Tesla · DoorDash · TikTok
A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.
Related questions
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test