Find each customer’s second order
Your task
For customers with at least two completed orders, return customer_id, the ID of their second completed order as second_order_id, and its ordered_on date. Sequence orders by date ascending, then ID ascending. Sort the result by customer ID.
Two orders can share a date. Use the order ID to decide which came first when the dates are equal.
Sample data
shop_orders| id | customer_id | ordered_on | channel | status | total_cents |
|---|---|---|---|---|---|
| 201 | 1 | 2025-01-04 | online | completed | 2500 |
| 202 | 2 | 2025-01-10 | store | completed | 1800 |
| 203 | 2 | 2025-01-10 | store | completed | 3200 |
| 204 | 3 | 2025-01-18 | marketplace | refunded | 5000 |
| 205 | 4 | 2025-02-02 | store | completed | 2700 |
| 206 | 1 | 2025-02-04 | online | completed | 4000 |
| 207 | 5 | 2025-02-18 | online | cancelled | 2400 |
| 208 | 6 | 2025-02-28 | online | completed | 1700 |
| 209 | 4 | 2025-03-16 | online | completed | 2900 |
| 210 | 8 | 2025-03-09 | store | completed | 3100 |
Expected output
| customer_id | second_order_id | ordered_on |
|---|---|---|
| 1 | 206 | 2025-02-04 |
| 2 | 203 | 2025-01-10 |
| 4 | 209 | 2025-03-16 |
Show a hint
Use ROW_NUMBER() over completed orders partitioned by customer ID, and keep row number two.
Customer and sales analytics guide
Define which orders count
This part of the record shop database tracks customer orders separately from the earlier album sales table. A completed order contributes its full price in cents. Refunded orders are fully refunded, and cancelled orders contribute zero. Decide whether you need to keep all statuses for a report or count only completed orders.
SELECT status, COUNT(*) AS orders
FROM shop_orders
GROUP BY status
ORDER BY status;Group at the customer level first
A customer can have several orders. Summarize by customer ID before counting customers or averaging customer-level values. To include customers with no completed orders, start with customers and put the completion filter in the LEFT JOIN condition.
SELECT c.id, COUNT(o.id) AS completed_orders
FROM customers AS c
LEFT JOIN shop_orders AS o
ON o.customer_id = c.id AND o.status = 'completed'
GROUP BY c.id
ORDER BY c.id;Choose a date boundary
Order and signup dates are stored as YYYY-MM-DD. A half-open date range includes its start and excludes its end. The example counts January orders by including January 1 and stopping before February 1.
SELECT COUNT(*) AS january_orders
FROM shop_orders
WHERE ordered_on >= '2025-01-01' AND ordered_on < '2025-02-01';Worked solution
WITH ranked AS (
SELECT customer_id, id, ordered_on,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_on, id) AS order_number
FROM shop_orders
WHERE status = 'completed'
)
SELECT customer_id, id AS second_order_id, ordered_on
FROM ranked
WHERE order_number = 2
ORDER BY customer_id;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
