Intermediate
Case 23 of 50
Case 023: Before and After
orders
"Before we can talk about anyone's buying pattern, I want to see the raw sequence first,"
the manager says. "For every order, could you show me the date of that same customer's previous order and their next order, side by side?"
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:
“For every order, show that same customer's previous order date and next order date, side by side.”
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.
SELECT
customer_id,
order_date,
LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order_date,
LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order_date
FROM orders
ORDER BY customer_id, order_date;
What that query returns
Case Debrief
LAG() and LEAD()
- LAG() looks backward to the previous row, LEAD() looks forward to the next one — both stay inside the same PARTITION BY group, so they never reach into a different customer's orders.
- For a customer's middle orders, both come back with a real date. For their very first order, LAG has nothing before it to find, so it returns NULL; for their most recent order, LEAD has nothing after it yet, so that's NULL too.