SQL Quest › SQL Interview Questions › Window Functions
Number Each Customer's Orders
The retention team wants to see how customers' baskets change from their first order to their second, third and beyond. Step one: label every order with which order it was for that customer — 1 for their first, 2 for their second, and so on, by order_date.
Show exactly these 4 columns, in this order: order_id, customer_id, order_date and order_number. Keep all 40 orders. Order by customer_id, then order_number.
The count starts again at 1 for every customer. A numbering that runs 1 to 40 across the whole table answers a different question.
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". Free accounts get 10 free challenge solves, and this can be one of them.
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: 40 rows — customer 1's orders are numbered 1 to 6, customer 10's 1 to 3
Hint
Concepts
SELECT Window Functions ROW_NUMBER PARTITION BY ORDER BY
Practise the topic: SQL practice questions · Window function practice · Ranking function practice
Read the concept: Window functions tutorial
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