Order sequence per customer
mediumOrder sequence per customer
Walmart SQL Interview Question
Walmart's product team wants to study how shoppers behave on their first online order compared with later ones.
Number each customer's orders from earliest to latest. Show the order_id, the customer_id, and the position as order_number, where a customer's earliest order is 1. Count every order, whatever its status.
Asked of
- Data Analyst
- Product Analyst
- Data Scientist
- Analytics Engineer
- Data Engineer
ordersTable35 rows
| Column Name | Type |
|---|---|
| order_id | BIGINT |
| customer_id | BIGINT |
| order_date | DATE |
| status | VARCHAR |
| amount | DOUBLE |
ordersExample Input
| order_id | customer_id | order_date | status | amount |
|---|---|---|---|---|
| 1001 | 1 | 2024-01-05 | completed | 120.5 |
| 1002 | 2 | 2024-01-07 | completed | 2400 |
| 1003 | 1 | 2024-01-11 | completed | 89.99 |
| 1004 | 3 | 2024-01-15 | canceled | 45 |
| 1005 | 4 | 2024-01-18 | completed | 610.75 |
| 1006 | 2 | 2024-01-22 | completed | 1850.25 |
| 1007 | 5 | 2024-01-29 | completed | 3200 |
| 1008 | 1 | 2024-02-02 | refunded | 75 |
| 1009 | 6 | 2024-02-06 | completed | 55.4 |
| 1010 | 3 | 2024-02-09 | completed | 132.6 |
Example Output
| order_id | customer_id | order_number |
|---|---|---|
| 1001 | 1 | 1 |
| 1002 | 2 | 1 |
| 1003 | 1 | 2 |
| 1004 | 3 | 1 |
| 1005 | 4 | 1 |
| 1006 | 2 | 2 |
| 1007 | 5 | 1 |
| 1008 | 1 | 3 |
| 1009 | 6 | 1 |
| 1010 | 3 | 2 |
Explanation
Customer 1 placed orders 1001, 1003 and 1008, in that order by date, so they are numbered 1, 2 and 3. The numbering starts again for every customer, so customer 2's first order, 1002, is also number 1.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Walmart
Difficulty
medium
Topic
window functions
Your query
Starting the SQL engine...