SQL Quest › SQL Interview Questions › Window Functions

Number Each Customer's Orders

EasyFreeQuerying BasicsWindow Functions

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_idcustomer_idproductcategoryquantitypricetotalorder_datecountrystatus
11Laptop ProElectronics11299.991299.992024-01-15USAcompleted
22Wireless MouseElectronics249.9999.982024-01-16Canadacompleted
33Office ChairFurniture1349.99349.992024-01-17USAcompleted

Expected output: 40 rows — customer 1's orders are numbered 1 to 6, customer 10's 1 to 3

Hint

Number the rows with a window function, and give the window two things: a group that restarts the count (the customer) and the order to count in (the order date).

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

Each Salary's Share of PayrollEasy · FreeRank Everyone by SalaryEasy · FreeEvery Employee Next to Their Department's Top SalaryEasy · FreeWindow Functions: ROW_NUMBERMedium · FreeRANK vs DENSE_RANK Side-by-SideMedium · FreeTop 3 Salary Tiers (DENSE_RANK)Medium · Free

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