Gaps and Islands: Pulling Out Contiguous Ranges

With window functions learned, this post is their most beautiful real-world use. “How many consecutive days did they log in”, “collapse consecutive dates into ranges”, “find the breaks in a sequence” — these look like different needs, but they’re the same classic problem: gaps and islands. The name sounds intimidating, but there’s a solution so elegant it’ll make you say “oh!” out loud.

What gaps and islands are

Build the picture first. Lay a sequence of ordered data (dates, serial numbers) out on a timeline: a contiguous run is an “island”, and the empty stretch between islands is a “gap”:

Move one: the magic of value − ROW_NUMBER

The most elegant solution is a single thought: contiguous values go up by 1 each step, and ROW_NUMBER also goes up by 1 each step, so “value − row_number” is a fixed constant within one island; hit a gap, and the value jumps while row_number doesn’t, so the difference changes. That constant becomes the island’s ID:

In SQL, collapsing consecutive login dates into ranges:

SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS days
FROM (
  SELECT user_id, login_date,
         login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::int AS grp
  FROM logins
) t
GROUP BY user_id, grp;   -- grp is the "island id": the same run of consecutive dates shares one grp

login_date − row_number is the same date (a constant) within one run of consecutive dates, and jumps the moment a day is skipped. One GROUP BY user_id, grp and each island collapses to one row; MIN/MAX give the start and end, COUNT the number of days.

Move two: LAG to find the breaks + running sum (more general)

The difference method is beautiful, but it has a precondition: “contiguous” must mean strictly +1. If your definition of contiguous is looser (“within 3 days counts as the same run”, “within the same session”), use the more general second move — compare with the previous row via LAG to find the breaks, then number them with a running sum:

SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date
FROM (
  SELECT user_id, login_date,
         SUM(is_new) OVER (PARTITION BY user_id ORDER BY login_date) AS island
  FROM (
    SELECT user_id, login_date,
           CASE WHEN login_date - LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) > 1
                THEN 1 ELSE 0 END AS is_new     -- more than 1 day after the previous row → a new island starts
    FROM logins
  ) a
) b
GROUP BY user_id, island;

Three layers: the innermost uses LAG to compare with the previous row and flags is_new = 1 when the threshold is exceeded (the first day of a new island); the middle layer adds those flags up with a running SUM — +1 at every new island, so island becomes the island id 1, 1, 1, 2, 2, 3…; the outer layer GROUP BYs it into ranges. The threshold (> 1) can be as loose as you like, which is what makes it more general than the difference method.

Reflections

Change the representation and the hard problem disappears

What fascinates me about value − row_number is what it demonstrates: many hard problems aren’t hard to solve; you’re using the wrong representation. The property “contiguous” is hard to express directly in SQL (there’s no IS CONSECUTIVE); but the moment you transform it into “a fixed constant”, the hard problem collapses into an utterly ordinary GROUP BY. That experience of “find the right representation and the problem solves itself” is the most satisfying moment in writing SQL — really in all analytical thinking. When I’m stuck now, I step back and ask: “is there a way to turn this odd property into something I already know how to handle?”

An elegant solution comes with preconditions you have to recognise

The difference method is beautiful, but only holds for “strictly +1”; real data is often less well behaved (weekends, a few days’ tolerance, irregular sessions). Forcing the difference method there gives wrong answers, and you switch to LAG + running sum — uglier, but with an adjustable threshold that handles every definition of “contiguous”. The lesson: elegant solutions usually have strict preconditions, and you confirm them before using them. My engineering judgement is simple — first ask “is my contiguity strictly +1”; if yes, take the difference method’s elegance; if not, take LAG’s generality. Which move to pick depends on the shape of the data, not on which is flashier.

Recognising the pattern is worth more than being able to write it

The hardest part of gaps and islands is often not writing it but recognising it — “periods of continuous online time”, “merge adjacent rows with the same state into one range”, “find the broken serial numbers” look unrelated on the surface and are all the same problem underneath. Recognise the pattern and you apply the move directly, instead of grinding from scratch every time and not necessarily getting it right. That’s why it gets its own post: half the value of window functions is “knowing how to use them”, the other half is “recognising which problems call for them”. Memorise the common problem shapes, and meeting one turns from “reinvent” into “apply” — the most concrete step up in data engineering skill I know.