Gaps and Islands

1. Why this happens

"How long is the current streak" sounds like it needs a loop — walk the dates one at a time, keep a running counter, reset it the moment a day is skipped. SQL doesn't have a natural "look at the previous row and remember something" primitive for that, so the instinct is to reach for whatever aggregate is lying around — MIN(), MAX(), COUNT() — and hope the math works out. It doesn't, because none of those functions know or care whether the days in between were actually present.

2. The dataset

Verified by actually running this against SQLite — not hand-computed. One user's daily check-ins over ten days, with two gaps: nothing on the 4th, and nothing on the 6th.

CREATE TABLE checkins(
  user_name TEXT,
  checkin_date TEXT
);

INSERT INTO checkins VALUES
 ('Aarav','2026-08-01'),
 ('Aarav','2026-08-02'),
 ('Aarav','2026-08-03'),
 ('Aarav','2026-08-05'),
 ('Aarav','2026-08-07'),
 ('Aarav','2026-08-08'),
 ('Aarav','2026-08-09'),
 ('Aarav','2026-08-10');

There are genuinely three separate streaks buried in here: the 1st–3rd (3 days), the 5th alone (1 day), and the 7th–10th (4 days) — the longest real streak is 4 days, not 8 and not 10.

3. The bug: span and count both lie about gaps

SELECT
  COUNT(DISTINCT checkin_date) AS total_checkin_days,
  julianday(MAX(checkin_date)) - julianday(MIN(checkin_date)) + 1 AS naive_span_days
FROM checkins;

COUNT(DISTINCT checkin_date) says 8 — true, but it's a total, not a streak; it has no idea whether those 8 days were consecutive or scattered across a year. MAX - MIN + 1 says 10 — the width of the date range, which silently assumes every single day inside that range was checked in, gaps included. Neither number is wrong about what it's measuring. Both are wrong the moment someone reads them as "the streak length," because neither one is actually checking for contiguity.

4. Why it happens

MIN, MAX, and COUNT each collapse a whole column down to one number, and in doing that they throw away the one thing that matters here: the order and adjacency of the rows relative to each other. A gap and a genuine unbroken run look identical to COUNT(DISTINCT ...) as long as the total number of days matches, and look identical to MAX - MIN as long as the first and last date match. Finding a real streak needs something that can tell "the day right after this one" from "the day three days after this one" — which means it needs each row's position relative to its neighbors, not just the column's overall min, max, or count.

5. The fix: a stable key for every consecutive run

SELECT checkin_date,
       ROW_NUMBER() OVER (ORDER BY checkin_date) AS rn,
       julianday(checkin_date) - ROW_NUMBER() OVER (ORDER BY checkin_date) AS island_key
FROM checkins
ORDER BY checkin_date;

This is the classic gaps-and-islands trick: subtract a plain row number from the date, converted to a number via julianday(). ROW_NUMBER() always increases by exactly 1 per row. When the date also increases by exactly 1 per row — a genuine consecutive run — the subtraction lands on the exact same value every time, because both sides are climbing in lockstep. The first three rows all land on 2461252.5. The moment a gap breaks that lockstep (the jump from the 3rd to the 5th), the date jumps by 2 but rn only jumps by 1, so the subtraction lands on a new value — and every row after that keeps landing on that new value, until the next gap shifts it again.

6. Turning the key into real streaks

WITH numbered AS (
  SELECT checkin_date,
         julianday(checkin_date) - ROW_NUMBER() OVER (ORDER BY checkin_date) AS island_key
  FROM checkins
)
SELECT MIN(checkin_date) AS streak_start,
       MAX(checkin_date) AS streak_end,
       COUNT(*) AS streak_length
FROM numbered
GROUP BY island_key
ORDER BY streak_start;

island_key is constant within a streak and different across streaks, so GROUP BY island_key does exactly what a hand-written loop would have done — it clusters consecutive dates together without ever needing to compare one row to the next explicitly. MIN/MAX/COUNT are the same functions from the buggy version, but now they're being asked to summarize one genuine island at a time instead of the whole table at once — which is the only thing that changed, and it's the whole fix.

7. Cheat sheet

8. Try it yourself

The trick generalizes past dates — subtracting a row number from any column that's supposed to increase by a fixed step per row (an integer ID, a sequence number) produces the same constant-within-a-run behavior, which is why "gaps and islands" shows up in inventory numbering and log sequence analysis just as often as it shows up in streak counting. The one thing worth double-checking every time: the ORDER BY inside the window function has to match the column being subtracted from, or the row numbers and the dates stop climbing in lockstep and the whole trick falls apart silently.

Practice window functions live on TableNotFound →