SQL Challenge #1 — Find the Customers Who Came Back 🧩
Let’s start with an interesting one!
You have an orders table:
order_id | customer_id | order_date | amount
---------|-------------|------------|-------
1 | 101 | 2026-01-05 | 500
2 | 102 | 2026-01-07 | 300
3 | 101 | 2026-01-20 | 700
4 | 103 | 2026-02-02 | 400
5 | 102 | 2026-02-10 | 600
6 | 101 | 2026-03-15 | 200
7 | 102 | 2026-03-18 | 800
8 | 103 | 2026-03-25 | 500
9 | 101 | 2026-04-01 | 900
Challenge 🎯
For each customer, find their longest gap in days between two consecutive orders.
Your output should contain:
customer_id
previous_order_date
next_order_date
gap_days
There’s a catch: If a customer has multiple gaps tied for their longest gap, return all of them.
For example, if a customer’s orders were:
Jan 1 → Jan 10 → Jan 19
Both gaps are 9 days, so both should be returned.
Rules
Don’t hardcode customer IDs.
Only compare consecutive orders.
Return all ties.
Try solving it without a self-join. 👀
Bonus: Can you solve it without using a CTE?
Drop your SQL approach below! If someone has already solved it, try finding a different approach. 👇