Streaks: orders on consecutive days (gaps and islands)
Find every streak where a customer ordered on two or more consecutive days. Show the customer, the first and last day of the streak, and its length in days.
What it tests
The "gaps and islands" technique, which turns consecutive runs into groups — a favourite hard question for login streaks, consecutive wins and uninterrupted sessions.
The pattern
Gaps and islands. "Consecutive", "in a row", "streak", "uninterrupted", "longest run".
How to approach it
- Reduce to one row per customer per day (DISTINCT), so two orders on one day don't break the count.
- Number each customer's days in order with ROW_NUMBER.
- Subtract the row number from the date (as a day number). Consecutive days give the same result — that value labels the island.
- Group by customer and that label: MIN date, MAX date and COUNT give the streak. Keep streaks of 2 or more.
Edge cases to mention
- Several orders on one day: DISTINCT first, or the row numbers jump and break the streak.
- Streaks that cross a month or year: day numbers (julianday, or date arithmetic) handle that; string tricks don't.
- "Longest streak per customer": wrap the result and keep the max length per customer, deciding how to break ties.
Common mistakes
- Forgetting DISTINCT on (customer, day): two orders on the same day then break the streak.
- Subtracting the row number from a date string instead of a day number.
- Using LAG alone, which finds pairs of consecutive days but not whole streaks.
Not true: Whole streaks: LAG by itself finds where each one starts and ends.
In fact: LAG finds pairs of days that follow each other, not where a streak starts and ends. Subtracting a row number from the day number gives every day of one streak the same value.
Part of Unit 8: SQL interview problems, from easy to hard in the free SQL course.