SQL interview question: Nth Purchase per Customer
Find Customer's Third Purchase
The analytics team wants to understand customer behavior by analyzing their third purchase specifically.
Return customer_id, order_id, and order_date for each customer's third order chronologically.
Only include customers who have at least 3 orders. Use ROW_NUMBER() window function.
Inspired by Uber's "User's Third Transaction" problem
Tables
orders 15 rows
- order_id integer PK
- customer_id integer → customers
- order_date text
- total_amount real
- status text
| order_id | customer_id | order_date | total_amount | status |
|---|---|---|---|---|
| 1 | 101 | 2024-01-20 | 249.99 | delivered |
| 2 | 101 | 2024-03-15 | 1999.99 | delivered |
| 4 | 101 | 2024-09-05 | 79.99 | delivered |