Intermediate
Case 29 of 50
Case 029: The Second Order
orders
"First orders are easy to find,"
the manager says. "Could you find the SECOND order for each customer? That's the one that tells us if they'll actually come back."
The data Alex is looking at
orders
| id | customer_id | order_date |
|---|---|---|
| 101 | 1 | 2026-01-05 |
| 102 | 2 | 2026-01-08 |
| 103 | 3 | 2026-01-12 |
| 104 | 1 | 2026-01-20 |
| 105 | 4 | 2026-02-01 |
| … 23 more rows | ||
TASK
Help Alex answer the manager:
“Find each customer's second order (chronologically).”
Write a query above and hit Run to see what comes back.
No hints requested yet — click "Get a hint" when you're stuck.
Alex's final query — this is for reference. Copy it into your own thinking, not into the editor above.
WITH ranked_orders AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
FROM orders
)
SELECT id, customer_id, order_date
FROM ranked_orders
WHERE rn = 2;
What that query returns
Case Debrief
CTE + ROW_NUMBER
- CTEs and window functions combine constantly — name the ranked version of your data, then filter on the rank in a plain WHERE.