Where this problem keeps showing up
A pattern I keep running into across LeetCode, StrataScratch, and DataLemur goes by a few different names — login streaks, consecutive active days, session length — but underneath, it's always the same question: given a list of events with no explicit grouping, how do you identify the runs of consecutive ones? SQL people call this the islands-and-gaps problem. An "island" is a run of consecutive values; a "gap" is what separates one island from the next.
This post works through three levels of solutions for that same family of problems. The first two use the same dataset and ask the same question — a Duolingo-style daily streak. The first level uses a brute-force method, and the second level upgrades it to a more elegant solution. The third keeps the same core idea but changes what "consecutive" means, moving from calendar dates to overlapping time ranges.
Level 1: streaks, the straightforward way
Duolingo tracks a daily streak: the number of consecutive calendar days a user has done at least one qualifying activity. The raw data looks like an activity log, not a streak count — one row per user action, with no column that tells you which streak a row belongs to. That has to be derived, but not before taking care of a couple of nuances in the dataset.
A streak day only counts if the user completed a lesson or a practice that day — a bare login or a review shows up as a real row in the table, but it doesn't save the streak. A user can also log more than one qualifying action on the same date (both a lesson and a practice, say), so deduplication still has to happen somewhere before anything else. And gaps are real gaps — days the user didn't show up at all — not a data quality issue to clean up.
The four users below each stress a different edge of that rule. Alice has a clean four-day run of lessons and practices, including two qualifying actions logged on the same date, immediately followed by a login-only day that looks like it continues the streak but doesn't, then a short gap, then a two-day close. Bob's four days of activity are all isolated from each other, and two of them — a login and a review — don't count as streak activity at all. Carol's eight days are all lessons and practices with no gaps and no traps, a clean baseline. Dave has a two-day streak, then what looks like a five-day run but is actually broken by a login-only day in the middle, then a long gap before one final lesson.
| user | activity_date | action | xp_earned |
|---|---|---|---|
| Alice | 2026-01-01 | lesson | 20 |
| Alice | 2026-01-02 | practice | 15 |
| Alice | 2026-01-03 | lesson | 20 |
| Alice | 2026-01-03 | practice | 15 |
| Alice | 2026-01-04 | practice | 15 |
| Alice | 2026-01-05 | login | 10 |
| Alice | 2026-01-06 | lesson | 20 |
| Alice | 2026-01-09 | login | 10 |
| Alice | 2026-01-10 | lesson | 20 |
| Alice | 2026-01-11 | practice | 15 |
| Bob | 2026-01-02 | login | 10 |
| Bob | 2026-01-05 | lesson | 20 |
| Bob | 2026-01-09 | practice | 15 |
| Bob | 2026-01-15 | review | 12 |
| Carol | 2026-01-01 | lesson | 20 |
| Carol | 2026-01-02 | practice | 15 |
| Carol | 2026-01-03 | lesson | 20 |
| Carol | 2026-01-04 | practice | 15 |
| Carol | 2026-01-05 | lesson | 20 |
| Carol | 2026-01-06 | practice | 15 |
| Carol | 2026-01-07 | lesson | 20 |
| Carol | 2026-01-08 | practice | 15 |
| Dave | 2026-01-03 | lesson | 20 |
| Dave | 2026-01-04 | practice | 15 |
| Dave | 2026-01-06 | lesson | 20 |
| Dave | 2026-01-07 | login | 10 |
| Dave | 2026-01-08 | practice | 15 |
| Dave | 2026-01-09 | lesson | 20 |
| Dave | 2026-01-10 | practice | 15 |
| Dave | 2026-01-20 | lesson | 20 |
The first-pass solution numbers each user's distinct qualifying dates in order and subtracts the row number from the date itself — every date inside the same unbroken run collapses to the same constant value, which becomes the group key for the streak.
WITH rnk_activity AS (
SELECT
user_name,
activity_date,
action,
xp_earned,
DENSE_RANK() OVER (PARTITION BY user_name ORDER BY activity_date ASC) AS rnk
FROM user_activity
WHERE action IN ('lesson', 'practice')
),
streak_groups AS (
SELECT
user_name,
DATEADD(DAY, 1 - rnk, activity_date) AS streak_group,
activity_date,
action,
xp_earned
FROM rnk_activity
)
SELECT
user_name,
streak_group,
MIN(activity_date) AS streak_start,
MAX(activity_date) AS streak_end,
DATEDIFF(DAY, MIN(activity_date), MAX(activity_date)) + 1 AS streak_length,
SUM(xp_earned) AS total_xp
FROM streak_groups
GROUP BY user_name, streak_group
ORDER BY user_name ASC, streak_start ASC
;
Filtering to action IN ('lesson', 'practice') inside rnk_activity, before ranking happens, is what keeps a login-only or review-only day from ever entering the sequence — those rows never get a rank at all, so they can't bridge two real streaks together. DENSE_RANK rather than ROW_NUMBER is the detail that makes Alice's January 3rd — a lesson and a practice on the same date — collapse correctly instead of silently shifting every rank after it by one. From there, activity_date minus rank is the classic trick: for any run of consecutive qualifying dates, that subtraction lands on the same constant value every time, which is exactly what streak_group needs to be to work as a GROUP BY key. xp_earned rides along through both CTEs so the final SUM only ever totals qualifying actions.
| user_name | streak_group | streak_start | streak_end | streak_length | total_xp |
|---|---|---|---|---|---|
| Alice | 2026-01-01 | 2026-01-01 | 2026-01-04 | 4 | 85 |
| Alice | 2026-01-02 | 2026-01-06 | 2026-01-06 | 1 | 20 |
| Alice | 2026-01-05 | 2026-01-10 | 2026-01-11 | 2 | 35 |
| Bob | 2026-01-05 | 2026-01-05 | 2026-01-05 | 1 | 20 |
| Bob | 2026-01-08 | 2026-01-09 | 2026-01-09 | 1 | 15 |
| Carol | 2026-01-01 | 2026-01-01 | 2026-01-08 | 8 | 140 |
| Dave | 2026-01-03 | 2026-01-03 | 2026-01-04 | 2 | 35 |
| Dave | 2026-01-04 | 2026-01-06 | 2026-01-06 | 1 | 20 |
| Dave | 2026-01-05 | 2026-01-08 | 2026-01-10 | 3 | 50 |
| Dave | 2026-01-14 | 2026-01-20 | 2026-01-20 | 1 | 20 |
Worth noting: once streak_start and streak_end are known, total_xp could just as easily come from a second pass — a subquery or join scoped to that date range and filtered to lesson/practice — instead of carrying xp_earned through both CTEs. Both get the same number here; carrying it through avoids scanning the table twice.
There's also a different order of operations that reaches the same result. GROUP BY user_name, activity_date first — still filtered to lesson/practice — collapses Alice's January 3rd down to one row before ranking ever happens. With duplicates already gone, a plain ROW_NUMBER works just as well as DENSE_RANK over what's left, since every user now has exactly one row per date. Same output either way: dedupe first and rank second, or rank in a way that tolerates duplicates.
Level 2: the same streaks, a more elegant way
Same table, same question, same expected output — but look at the streak_group column in the results above. Alice's second streak groups under 2026-01-02, even though that streak actually runs from Jan 6 to Jan 6. That value isn't a date anyone would recognize or want to query against — it's a side effect of subtracting a rank from a date, kept around only because it happens to be unique per streak. Grouping by something that looks like a date, but is not an actual date, is a sign this technique, while correct, isn't the one to reach for by default.
It also only works because subtracting a row number from a date happens to line up perfectly for consecutive integers and consecutive calendar days — a clever coincidence more than a general technique, and one that doesn't extend to Level 3's problem below, where "consecutive" isn't a fixed one-day step.
A better solution to this problem would be to compare each row to whatever came immediately before it for that user, and mark the exact spot where the streak breaks. Running a cumulative count of those breaks gives every row a stable streak identifier — a small integer that means exactly what it looks like, streak number 1, 2, 3 — without ever touching a row number or a fabricated date.
WITH prev_records AS (
SELECT
user_name,
activity_date,
SUM(xp_earned) AS total_xp,
LAG(activity_date, 1) OVER (PARTITION BY user_name ORDER BY activity_date ASC) AS prev_date
FROM user_activity
WHERE action IN ('lesson', 'practice')
GROUP BY user_name, activity_date
),
streak_ids AS (
SELECT
user_name,
SUM(
CASE
WHEN DATEDIFF(DAY, prev_date, activity_date) = 1 THEN 0
ELSE 1
END
) OVER(PARTITION BY user_name ORDER BY activity_date ASC) AS streak_id,
activity_date,
total_xp
FROM prev_records
)
SELECT
user_name,
streak_id,
MIN(activity_date) AS streak_start,
MAX(activity_date) AS streak_end,
DATEDIFF(DAY, MIN(activity_date), MAX(activity_date)) + 1 AS streak_length,
SUM(total_xp) AS total_xp
FROM streak_ids
GROUP BY user_name, streak_id
ORDER BY user_name ASC, streak_id ASC
;
prev_records does the deduplication up front — GROUP BY user_name, activity_date with SUM(xp_earned), still filtered to lesson/practice, collapses Alice's January 3rd into one row before LAG or anything else sees it. That's the same "dedupe first, then rank" order of operations mentioned above for Level 1.
A detail worth noting is what happens on each user's very first qualifying date, where LAG has nothing before it to return and prev_date is NULL. DATEDIFF(DAY, NULL, activity_date) is itself NULL, and NULL = 1 is neither true nor false — it's unknown — so the CASE falls through to ELSE 1 rather than erroring or silently defaulting to 0. That's what seeds every user's streak counter at 1 instead of 0, for free, without an explicit first-row special case. From there, the running SUM of those 0/1 flags, partitioned by user and ordered by date, only increments when a real gap shows up — giving every row a small integer streak id instead of a fabricated date.
| user_name | streak_id | streak_start | streak_end | streak_length | total_xp |
|---|---|---|---|---|---|
| Alice | 1 | 2026-01-01 | 2026-01-04 | 4 | 85 |
| Alice | 2 | 2026-01-06 | 2026-01-06 | 1 | 20 |
| Alice | 3 | 2026-01-10 | 2026-01-11 | 2 | 35 |
| Bob | 1 | 2026-01-05 | 2026-01-05 | 1 | 20 |
| Bob | 2 | 2026-01-09 | 2026-01-09 | 1 | 15 |
| Carol | 1 | 2026-01-01 | 2026-01-08 | 8 | 140 |
| Dave | 1 | 2026-01-03 | 2026-01-04 | 2 | 35 |
| Dave | 2 | 2026-01-06 | 2026-01-06 | 1 | 20 |
| Dave | 3 | 2026-01-08 | 2026-01-10 | 3 | 50 |
| Dave | 4 | 2026-01-20 | 2026-01-20 | 1 | 20 |
Same streaks, same totals as Level 1 — streak_id is just 1, 2, 3, 4 per user instead of a date nobody would recognize.
Level 3: from streaks to sessions
Same island-and-gap idea, harder problem. Instead of a daily streak, this is total unique minutes of user activity from a table of session logon/logout times — and sessions can overlap. A user might log on for a call at 9:00 and log off at 9:30, then a second device logs on at 9:15 and off at 9:45. Those two sessions overlap for fifteen minutes, so simply summing each row's elapsed time double-counts that overlap.
This is where "consecutive" stops meaning "the next calendar day" and starts meaning "the next time range doesn't start before the current one ends" — same underlying island-and-gap structure, applied to intervals instead of dates.
Five users, five different overlap shapes. Erin is a basic two-session overlap plus one clean separate session. Frank is one session fully nested inside another. Grace is the one worth staring at before writing anything — two sessions are both nested inside a longer one, but there's a gap between them, which breaks any solution that only ever compares a row to the one directly before it. Hank has no overlaps at all, a sanity baseline where a simple sum of the elapsed_time column happens to be correct. Ivy is a three-way chain: the first and last session never touch directly, but both overlap the one in the middle, so all three still merge into a single session.
| user | logon | logout | elapsed_time |
|---|---|---|---|
| Erin | 2026-02-01 09:00:00 | 2026-02-01 09:30:00 | 30 |
| Erin | 2026-02-01 09:15:00 | 2026-02-01 09:45:00 | 30 |
| Erin | 2026-02-01 10:00:00 | 2026-02-01 10:10:00 | 10 |
| Frank | 2026-02-01 09:00:00 | 2026-02-01 10:00:00 | 60 |
| Frank | 2026-02-01 09:10:00 | 2026-02-01 09:20:00 | 10 |
| Grace | 2026-02-01 09:00:00 | 2026-02-01 10:00:00 | 60 |
| Grace | 2026-02-01 09:10:00 | 2026-02-01 09:20:00 | 10 |
| Grace | 2026-02-01 09:30:00 | 2026-02-01 09:40:00 | 10 |
| Grace | 2026-02-01 10:30:00 | 2026-02-01 10:45:00 | 15 |
| Hank | 2026-02-01 08:00:00 | 2026-02-01 08:20:00 | 20 |
| Hank | 2026-02-01 09:00:00 | 2026-02-01 09:10:00 | 10 |
| Hank | 2026-02-01 14:00:00 | 2026-02-01 14:05:00 | 5 |
| Ivy | 2026-02-01 09:00:00 | 2026-02-01 09:20:00 | 20 |
| Ivy | 2026-02-01 09:15:00 | 2026-02-01 09:40:00 | 25 |
| Ivy | 2026-02-01 09:35:00 | 2026-02-01 10:00:00 | 25 |
WITH running_max AS (
SELECT
user_name,
logon,
logout,
MAX(logout)
OVER(PARTITION BY user_name ORDER BY logon ASC, logout DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS running_max_logout,
elapsed_time
FROM user_sessions
),
session_ids AS (
SELECT
user_name,
SUM(
CASE
WHEN logon <= running_max_logout THEN 0
ELSE 1
END
) OVER(PARTITION BY user_name ORDER BY logon ASC, logout DESC) AS session_id,
logon,
logout,
running_max_logout
FROM running_max
),
session_totals AS (
SELECT
user_name,
session_id,
MIN(logon) AS session_start,
MAX(logout) AS session_end,
DATEDIFF(MINUTE, MIN(logon), MAX(logout)) AS session_duration
FROM session_ids
GROUP BY user_name, session_id
)
SELECT
user_name,
SUM(session_duration) AS total_elapsed_time,
COUNT(session_id) AS total_sessions
FROM session_totals
GROUP BY user_name
ORDER BY user_name ASC
;
running_max is the load-bearing piece: for each row, MAX(logout) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) looks at every session that comes before it for that user — not just the one directly before it — and takes the latest logout seen so far. session_ids then compares the current row's logon against that running max instead of against a single neighbor. That distinction is exactly what Grace's data is built to test: her second and third sessions (09:10–09:20 and 09:30–09:40) are both nested inside her first (09:00–10:00), but there's a gap between the two nested ones. A solution that only looks at the immediately preceding row would see the third session start (09:30) after the second one ends (09:20) and wrongly call it a new session. Comparing against the running max instead — which by that point already holds 10:00 from the first session — correctly keeps all three merged into one 60-minute block.
The first row for each user hits the same NULL case as Level 2: nothing precedes it, so running_max_logout is NULL, logon <= NULL is unknown rather than true or false, and the CASE falls through to ELSE 1 — correctly starting a new session rather than erroring.
Below is the output of the session_totals CTE:
| user_name | session_id | session_start | session_end | session_duration |
|---|---|---|---|---|
| Erin | 1 | 2026-02-01 09:00:00 | 2026-02-01 09:45:00 | 45 |
| Erin | 2 | 2026-02-01 10:00:00 | 2026-02-01 10:10:00 | 10 |
| Frank | 1 | 2026-02-01 09:00:00 | 2026-02-01 10:00:00 | 60 |
| Grace | 1 | 2026-02-01 09:00:00 | 2026-02-01 10:00:00 | 60 |
| Grace | 2 | 2026-02-01 10:30:00 | 2026-02-01 10:45:00 | 15 |
| Hank | 1 | 2026-02-01 08:00:00 | 2026-02-01 08:20:00 | 20 |
| Hank | 2 | 2026-02-01 09:00:00 | 2026-02-01 09:10:00 | 10 |
| Hank | 3 | 2026-02-01 14:00:00 | 2026-02-01 14:05:00 | 5 |
| Ivy | 1 | 2026-02-01 09:00:00 | 2026-02-01 10:00:00 | 60 |
session_totals already has one row per merged, non-overlapping session, so a final GROUP BY user_name is safe from here — no double-counting risk left to introduce:
| user_name | total_elapsed_time | total_sessions |
|---|---|---|
| Erin | 55 | 2 |
| Frank | 60 | 1 |
| Grace | 75 | 2 |
| Hank | 35 | 3 |
| Ivy | 60 | 1 |
One more detail worth naming: the secondary sort is logout DESC, tie-breaking same-logon rows longest-first. logout ASC will yield the same results since our condition uses <= in WHEN logon <= running_max_logout THEN 0.
Suppose we use just < instead, i.e. WHEN logon < running_max_logout THEN 0, and there are two rows that tie for logon time. One row has the same logon and logout times at 2026-02-01 09:00:00. The second row has logon at 2026-02-01 09:00:00 and logout at 2026-02-01 09:10:00. In this case logout DESC will merge them both into one session, but logout ASC will keep them as two separate sessions. There won't be any impact on the total_elapsed_time in the final result though, since the session_duration of this extra session will be 0.
Another case where even logout DESC fails to merge the sessions will be when both of the tied rows have the same logon and logout times, both at 2026-02-01 09:00:00. Only using <= in WHEN logon <= running_max_logout THEN 0 will merge them into one session.
All that being said, it is all just personal preference. Separate sessions or not, they don't change the total_elapsed_time for a user. To me it seemed cleaner to merge them into one session using <=. Out of DESC and ASC, I went with DESC since it puts the longest session in a tied group at the top, letting the running MAX pick up the maximum logout time up front.
Takeaway
When you come across an islands and gaps problem for the first time, at first glance it seems confusing. But then it also seems like the solution is at the tip of your tongue, or your fingers in this case I suppose. Only when you have sat with this problem, staring at the screen for hours, and figured out the solution to it, do you realize that it is not a difficult problem by any means — it's just a smart one, a tricky one.
The trick is to compare the current row to what came before it and flag the breaks in the pattern. All three levels have different ways of doing that: a fabricated date in Level 1, the immediately preceding row's date in Level 2, a running max of everything preceding in Level 3. Once you flag the breaks, a running count of them gives you a stable group id.