Streaks: orders on consecutive days (gaps and islands)

Step 1 Solve it yourself first

Tables you will use

orders customers

What it really 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".

Picture it

flowchart LR
  D["Priya's days<br/>01-06 · 01-07 · 01-08 · 02-02 · 03-10"] --> N["minus row number<br/>1 · 2 · 3 · 4 · 5"]
  N --> G["labels<br/>01-05 · 01-05 · 01-05 · 01-29 · 03-05"]
  G --> I["same label = one streak<br/>01-06 to 01-08: 3 days"]

The approach, step by step

1. Reduce to one row per customer per day (DISTINCT), so two orders on one day don't break the count.
2. Number each customer's days in order with ROW_NUMBER.
3. Subtract the row number from the date (as a day number). Consecutive days give the same result — that value labels the island.
4. Group by customer and that label: MIN date, MAX date and COUNT give the streak. Keep streaks of 2 or more.

WITH days AS (
  SELECT DISTINCT customer_id, order_date
  FROM orders
),
labelled AS (
  SELECT customer_id, order_date,
         julianday(order_date)
           - ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS island
  FROM days
)
SELECT c.name, MIN(l.order_date) AS streak_start, MAX(l.order_date) AS streak_end,
       COUNT(*) AS days_in_a_row
FROM labelled l
JOIN customers c ON c.customer_id = l.customer_id
GROUP BY l.customer_id, c.name, l.island
HAVING COUNT(*) >= 2
ORDER BY days_in_a_row DESC, c.name;

WITH days AS (
  SELECT DISTINCT customer_id, order_date
  FROM orders
),
flagged AS (
  SELECT customer_id, order_date,
         CASE WHEN julianday(order_date)
                 - julianday(LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)) = 1
              THEN 0 ELSE 1 END AS starts_streak
  FROM days
),
numbered AS (
  SELECT customer_id, order_date,
         SUM(starts_streak) OVER (PARTITION BY customer_id ORDER BY order_date) AS streak_id
  FROM flagged
)
SELECT customer_id, MIN(order_date) AS streak_start, MAX(order_date) AS streak_end,
       COUNT(*) AS days_in_a_row
FROM numbered
GROUP BY customer_id, streak_id
HAVING COUNT(*) >= 2
ORDER BY days_in_a_row DESC;

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Watch

  SQL gaps and islands (video)

  YouTube search, free

• Practice

  DataLemur — consecutive days questions

  real interview questions, many free

• Practice

  LeetCode — Top SQL 50 study plan

  free account

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain, with Priya's dates, why date minus row number is constant inside a streak.

   Show answer

   Priya ordered on 6, 7 and 8 January, 2 February and 10 March. Number them 1 to 5 in date order, and subtract the number from the date:

   • 6 Jan − 1 = 5 Jan
   • 7 Jan − 2 = 5 Jan
   • 8 Jan − 3 = 5 Jan
   • 2 Feb − 4 = 29 Jan
   • 10 Mar − 5 = 5 Mar

   Inside a streak, the date goes up by one day and the row number goes up by one, so their difference stays the same: 5 January for all three. A gap makes the date jump by more than one while the row number still only goes up by one, so the difference changes. That difference is a label for each island: group by it, and each group is one streak.

2. Writefrom a blank page, then run it

   Find the longest run of consecutive order days across all customers combined.

   Show answer

   Same trick, without PARTITION BY: take every distinct date on which anyone ordered, number them in order, and group by date minus row number. The longest run is 6 to 8 January (3 days, all Priya's). Every other order date stands alone.

   sqlCopy

   WITH days AS (
     SELECT DISTINCT order_date
     FROM orders
   ),
   labelled AS (
     SELECT order_date,
            julianday(order_date) - ROW_NUMBER() OVER (ORDER BY order_date) AS island
     FROM days
   )
   SELECT MIN(order_date) AS run_start, MAX(order_date) AS run_end,
          COUNT(*) AS days_in_a_row
   FROM labelled
   GROUP BY island
   ORDER BY days_in_a_row DESC, run_start
   LIMIT 1;

   Try it

   1 row

   run_start	run_end	days_in_a_row
   2024-01-06	2024-01-08	3

3. Predictsay it before you run it

   If Priya also ordered on 2024-01-09, what would the solution return?

   Show answer

   One streak for Priya, now 4 days long: 6 to 9 January. 9 January is row 4, and 9 Jan − 4 = 5 Jan, the same label as the other three, so it joins their island.

   sqlCopy

   INSERT INTO orders VALUES (111, 1, '2024-01-09', 10, 'delivered');
   WITH days AS (
     SELECT DISTINCT customer_id, order_date
     FROM orders
   ),
   labelled AS (
     SELECT customer_id, order_date,
            julianday(order_date)
              - ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS island
     FROM days
   )
   SELECT c.name, MIN(l.order_date) AS streak_start, MAX(l.order_date) AS streak_end,
          COUNT(*) AS days_in_a_row
   FROM labelled l
   JOIN customers c ON c.customer_id = l.customer_id
   GROUP BY l.customer_id, c.name, l.island
   HAVING COUNT(*) >= 2
   ORDER BY days_in_a_row DESC, c.name;

   Try it

   1 row

   name	streak_start	streak_end	days_in_a_row
   Priya	2024-01-06	2024-01-09	4

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Add a second order for Priya on 2024-01-07 and show that the solution still gives a 3-day streak. Then remove DISTINCT and see what breaks.

   Show answer

   With DISTINCT, the second order on 7 January collapses into the same day, so the dates are still 6, 7, 8 and the solution still reports one 3-day streak.

   Without DISTINCT (below), the days are 6, 7, 7, 8 numbered 1 to 4. The labels become 5 Jan, 5 Jan, 4 Jan, 4 Jan: the repeated day pushes the row number ahead of the date, so the streak splits into two "2-day streaks" (6–7 and 7–8), and 7 January is counted twice. DISTINCT matters because the trick assumes one row per day.

   sqlCopy

   INSERT INTO orders VALUES (111, 1, '2024-01-07', 10, 'delivered');
   WITH days AS (
     SELECT customer_id, order_date
     FROM orders
   ),
   labelled AS (
     SELECT customer_id, order_date,
            julianday(order_date)
              - ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS island
     FROM days
   )
   SELECT c.name, MIN(l.order_date) AS streak_start, MAX(l.order_date) AS streak_end,
          COUNT(*) AS days_in_a_row
   FROM labelled l
   JOIN customers c ON c.customer_id = l.customer_id
   GROUP BY l.customer_id, c.name, l.island
   HAVING COUNT(*) >= 2
   ORDER BY streak_start;

   Try it

   2 rows

   name	streak_start	streak_end	days_in_a_row
   Priya	2024-01-06	2024-01-07	2
   Priya	2024-01-07	2024-01-08	2

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

This is gaps and islands. I first reduce to distinct customer-days, number each customer's days with ROW_NUMBER, and subtract that number from the date: within a run of consecutive days both go up by one, so the difference is constant and labels the island. Grouping by customer and that label gives each streak's start, end and length, and HAVING keeps the ones of two days or more.

Follow-up questions

Tick each check when you can do it without notes