{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"gaps-and-islands","title":"Streaks and sessions: gaps and islands","url":"https://addaly.com/learn/data-and-sql/gaps-and-islands","position":40,"of":66,"minutes":9,"answer":"Label each run of consecutive values with date minus row_number, or each session with a running sum of gap flags, then group by the label.","keyIdea":"Label each run of consecutive values with date minus row_number, or each session with a running sum of gap flags, then group by the label; the gap threshold is a definition that changes every downstream count."},"body":{"format":"markdown","text":"## Two shapes of the same problem\n\n\"How long is this user's current daily-login streak?\" and \"split this stream of clicks into visits\" look unrelated. Both are the same operation: walk the rows in order, decide where one run ends and the next begins, and number the runs. SQL people call the runs *islands* and the breaks *gaps*, and there are two tricks, one for each shape.\n\n## Islands of consecutive values\n\nA user logged in on 3, 4, 5, 9, 10 and 14 March. That is three streaks. Number the rows and subtract:\n\n```sql\nSELECT user_id, login_day,\n       login_day - (row_number() OVER (PARTITION BY user_id ORDER BY login_day))::int AS grp\nFROM logins;\n```\n\nFor the six days the row numbers are 1 to 6, and `login_day - row_number` gives 2 March, 2 March, 2 March, 5 March, 5 March, 8 March. Within a streak, the date and the row number advance together, so their difference stays constant. At a gap the date jumps and the row number does not, so the difference changes. That constant is an arbitrary label for the streak, and grouping by it finishes the job:\n\n```sql\nWITH g AS (... the query above ...)\nSELECT user_id, min(login_day) AS streak_start, max(login_day) AS streak_end,\n       count(*) AS days\nFROM g\nGROUP BY user_id, grp\nORDER BY user_id, streak_start;\n```\n\nThe longest streak per user is a `max(days)` over that; the current streak is the island whose `streak_end` is today. The trick works on any evenly spaced sequence: consecutive invoice numbers, consecutive weeks, consecutive months if you convert months to a running integer first. It needs each value to appear once per partition, so deduplicate a login table with several logins a day before applying it.\n\n## Sessions defined by a gap in time\n\nClicks are not evenly spaced, so the row-number trick does not apply. Instead, decide the gap that separates two sessions, flag each row that starts a new one, and take a running count of the flags:\n\n```sql\nWITH flagged AS (\n  SELECT user_id, clicked_at,\n         CASE WHEN clicked_at - lag(clicked_at) OVER (PARTITION BY user_id ORDER BY clicked_at)\n                   > interval '30 minutes'\n              OR lag(clicked_at) OVER (PARTITION BY user_id ORDER BY clicked_at) IS NULL\n              THEN 1 ELSE 0 END AS new_session\n  FROM clicks\n),\nnumbered AS (\n  SELECT *, sum(new_session) OVER (PARTITION BY user_id ORDER BY clicked_at\n                                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_no\n  FROM flagged\n)\nSELECT user_id, session_no,\n       min(clicked_at) AS started, max(clicked_at) AS ended,\n       max(clicked_at) - min(clicked_at) AS duration,\n       count(*) AS clicks\nFROM numbered\nGROUP BY user_id, session_no;\n```\n\nThree layers: compare each row with its predecessor and flag a break; running-sum the flags so that every row carries its session number; group by that number. The first row of each user has no predecessor and is flagged as a session start. Every layer uses something from earlier in this module, and this is the query where they all meet.\n\n> Figure: Six logins, three streaks, and the label that finds them. Timeline: 3 Mar, row 1, 3 March minus 1 day is 2 March: the label for this island; 4 Mar, row 2, 4 March minus 2 days is 2 March. Same label, same streak; 5 Mar, row 3, 5 March minus 3 days is 2 March. The streak is three days long; 9 Mar, row 4, Three days were missed, so the label jumps to 5 March: a new island; 10 Mar, row 5, Label 5 March again. This streak is two days long; 14 Mar, row 6, Label 8 March. Grouping by the label gives streaks of three, two and one day. Within a streak the date and the row number advance together, so their difference holds still. At a gap the date jumps and the row number does not, so the difference changes. That constant is an arbitrary name for the streak, and grouping by it finishes the job.\n\n## The threshold is a definition, not a fact\n\nThirty minutes of inactivity is the convention web analytics tools inherited from the 1990s. It is not a property of the data. Change it to 10 minutes and a user who reads a long article becomes two sessions; change it to two hours and an afternoon of intermittent browsing becomes one. The number of sessions per user, the average session length and every \"conversion per session\" rate move with the threshold, sometimes by a factor of two. Write the threshold into the query as a named constant, put it in the report's caption, and be suspicious of any session count compared across two systems that may not share it.\n\n## Where the islands trick pays for itself\n\n- Machine uptime from a status log: islands of `status = 'up'` give outage windows and their lengths.\n- Membership history: islands of consecutive paid months give each tenure, and the gaps are churn-and-return events.\n- Data quality: islands in a sequence that should be unbroken (invoice numbers, sensor readings every minute) surface the exact ranges that are missing.\n\nEach of these is a `min`, `max` and `count` per island, once the island has a label.\n\n## The habit\n\nDraw six rows on paper before writing either query. Mark where the runs break. Then decide whether the break is \"the next value is not adjacent\" (row-number trick) or \"the next value is too far away\" (lag-and-flag trick). The wrong choice produces a query that runs and returns something plausible, which is the recurring theme of this course."},"check":{"question":"A user logged in on 3, 4, 5, 9 and 10 March. Using `login_day - row_number() OVER (ORDER BY login_day)` to label streaks, what labels do the five rows get, and why does the method work?","options":[{"text":"3, 3, 3, 9, 9 March; the label is each streak's first date, because row_number restarts at 1 whenever the previous day is missing from the partition.","correct":false,"tempting":"row_number never restarts inside a partition; the labels are date minus a running count, which happens to land on the day before each streak began."},{"text":"1, 1, 1, 2, 2; row_number restarts whenever the date is not adjacent to the previous one, producing a streak counter directly.","correct":false,"tempting":"row_number never restarts within a partition; the labels are dates, and the counter effect comes from the subtraction."},{"text":"2, 2, 2, 4, 4 March; the three-day gap between 5 and 9 March advances the label by the number of missing days, so later streaks are labelled by their gap length.","correct":false,"tempting":"Missing days do not advance the row number, so the label jumps by the whole gap: 9 March minus row 4 is 5 March, not 4 March."},{"text":"2, 2, 2, 5, 5 March; inside a streak the date and the row number rise together, so their difference only changes where a day is skipped.","correct":true}]},"prev":{"slug":"moving-averages","title":"Moving averages: seven rows is not seven days","url":"https://addaly.com/learn/data-and-sql/moving-averages"},"next":{"slug":"cohorts-and-retention","title":"Cohorts and retention: how a month of new customers behaves afterwards","url":"https://addaly.com/learn/data-and-sql/cohorts-and-retention"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Streaks and sessions: gaps and islands', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/gaps-and-islands (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Streaks and sessions: gaps and islands","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":4,"slug":"windows-sequences-and-time","title":"Windows, sequences and time"},"answer":null,"keyIdea":"Label each run of consecutive values with date minus row_number, or each session with a running sum of gap flags, then group by the label; the gap threshold is a definition that changes every downstream count.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"timeline","title":"Six logins, three streaks, and the label that finds them","events":[{"when":"3 Mar, row 1","what":"3 March minus 1 day is 2 March: the label for this island"},{"when":"4 Mar, row 2","what":"4 March minus 2 days is 2 March. Same label, same streak"},{"when":"5 Mar, row 3","what":"5 March minus 3 days is 2 March. The streak is three days long"},{"when":"9 Mar, row 4","what":"Three days were missed, so the label jumps to 5 March: a new island"},{"when":"10 Mar, row 5","what":"Label 5 March again. This streak is two days long"},{"when":"14 Mar, row 6","what":"Label 8 March. Grouping by the label gives streaks of three, two and one day"}],"caption":"Within a streak the date and the row number advance together, so their difference holds still. At a gap the date jumps and the row number does not, so the difference changes. That constant is an arbitrary name for the streak, and grouping by it finishes the job."},"alt":"Figure: Six logins, three streaks, and the label that finds them. Timeline: 3 Mar, row 1, 3 March minus 1 day is 2 March: the label for this island; 4 Mar, row 2, 4 March minus 2 days is 2 March. Same label, same streak; 5 Mar, row 3, 5 March minus 3 days is 2 March. The streak is three days long; 9 Mar, row 4, Three days were missed, so the label jumps to 5 March: a new island; 10 Mar, row 5, Label 5 March again. This streak is two days long; 14 Mar, row 6, Label 8 March. Grouping by the label gives streaks of three, two and one day. Within a streak the date and the row number advance together, so their difference holds still. At a gap the date jumps and the row number does not, so the difference changes. That constant is an arbitrary name for the streak, and grouping by it finishes the job."}},"blocks":[{"t":"h2","id":"two-shapes-of-the-same-problem","runs":[{"text":"Two shapes of the same problem"}]},{"t":"p","runs":[{"text":"“How long is this user’s current daily-login streak?” and “split this stream of clicks into visits” look unrelated. Both are the same operation: walk the rows in order, decide where one run ends and the next begins, and number the runs. SQL people call the runs "},{"text":"islands","i":true},{"text":" and the breaks "},{"text":"gaps","i":true},{"text":", and there are two tricks, one for each shape."}]},{"t":"h2","id":"islands-of-consecutive-values","runs":[{"text":"Islands of consecutive values"}]},{"t":"p","runs":[{"text":"A user logged in on 3, 4, 5, 9, 10 and 14 March. That is three streaks. Number the rows and subtract:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT user_id, login_day,\n       login_day - (row_number() OVER (PARTITION BY user_id ORDER BY login_day))::int AS grp\nFROM logins;"},{"t":"p","runs":[{"text":"For the six days the row numbers are 1 to 6, and "},{"text":"login_day - row_number","code":true},{"text":" gives 2 March, 2 March, 2 March, 5 March, 5 March, 8 March. Within a streak, the date and the row number advance together, so their difference stays constant. At a gap the date jumps and the row number does not, so the difference changes. That constant is an arbitrary label for the streak, and grouping by it finishes the job:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH g AS (... the query above ...)\nSELECT user_id, min(login_day) AS streak_start, max(login_day) AS streak_end,\n       count(*) AS days\nFROM g\nGROUP BY user_id, grp\nORDER BY user_id, streak_start;"},{"t":"p","runs":[{"text":"The longest streak per user is a "},{"text":"max(days)","code":true},{"text":" over that; the current streak is the island whose "},{"text":"streak_end","code":true},{"text":" is today. The trick works on any evenly spaced sequence: consecutive invoice numbers, consecutive weeks, consecutive months if you convert months to a running integer first. It needs each value to appear once per partition, so deduplicate a login table with several logins a day before applying it."}]},{"t":"h2","id":"sessions-defined-by-a-gap-in-time","runs":[{"text":"Sessions defined by a gap in time"}]},{"t":"p","runs":[{"text":"Clicks are not evenly spaced, so the row-number trick does not apply. Instead, decide the gap that separates two sessions, flag each row that starts a new one, and take a running count of the flags:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH flagged AS (\n  SELECT user_id, clicked_at,\n         CASE WHEN clicked_at - lag(clicked_at) OVER (PARTITION BY user_id ORDER BY clicked_at)\n                   > interval '30 minutes'\n              OR lag(clicked_at) OVER (PARTITION BY user_id ORDER BY clicked_at) IS NULL\n              THEN 1 ELSE 0 END AS new_session\n  FROM clicks\n),\nnumbered AS (\n  SELECT *, sum(new_session) OVER (PARTITION BY user_id ORDER BY clicked_at\n                                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_no\n  FROM flagged\n)\nSELECT user_id, session_no,\n       min(clicked_at) AS started, max(clicked_at) AS ended,\n       max(clicked_at) - min(clicked_at) AS duration,\n       count(*) AS clicks\nFROM numbered\nGROUP BY user_id, session_no;"},{"t":"p","runs":[{"text":"Three layers: compare each row with its predecessor and flag a break; running-sum the flags so that every row carries its session number; group by that number. The first row of each user has no predecessor and is flagged as a session start. Every layer uses something from earlier in this module, and this is the query where they all meet."}]},{"t":"h2","id":"the-threshold-is-a-definition-not-a-fact","runs":[{"text":"The threshold is a definition, not a fact"}]},{"t":"p","runs":[{"text":"Thirty minutes of inactivity is the convention web analytics tools inherited from the 1990s. It is not a property of the data. Change it to 10 minutes and a user who reads a long article becomes two sessions; change it to two hours and an afternoon of intermittent browsing becomes one. The number of sessions per user, the average session length and every “conversion per session” rate move with the threshold, sometimes by a factor of two. Write the threshold into the query as a named constant, put it in the report’s caption, and be suspicious of any session count compared across two systems that may not share it."}]},{"t":"h2","id":"where-the-islands-trick-pays-for-itself","runs":[{"text":"Where the islands trick pays for itself"}]},{"t":"ul","items":[[{"text":"Machine uptime from a status log: islands of "},{"text":"status = 'up'","code":true},{"text":" give outage windows and their lengths."}],[{"text":"Membership history: islands of consecutive paid months give each tenure, and the gaps are churn-and-return events."}],[{"text":"Data quality: islands in a sequence that should be unbroken (invoice numbers, sensor readings every minute) surface the exact ranges that are missing."}]]},{"t":"p","runs":[{"text":"Each of these is a "},{"text":"min","code":true},{"text":", "},{"text":"max","code":true},{"text":" and "},{"text":"count","code":true},{"text":" per island, once the island has a label."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"Draw six rows on paper before writing either query. Mark where the runs break. Then decide whether the break is “the next value is not adjacent” (row-number trick) or “the next value is too far away” (lag-and-flag trick). The wrong choice produces a query that runs and returns something plausible, which is the recurring theme of this course."}]}],"check":{"question":[{"text":"A user logged in on 3, 4, 5, 9 and 10 March. Using "},{"text":"login_day - row_number() OVER (ORDER BY login_day)","code":true},{"text":" to label streaks, what labels do the five rows get, and why does the method work?"}],"options":[{"text":[{"text":"3, 3, 3, 9, 9 March; the label is each streak’s first date, because row_number restarts at 1 whenever the previous day is missing from the partition."}],"correct":false,"tempting":[{"text":"row_number never restarts inside a partition; the labels are date minus a running count, which happens to land on the day before each streak began."}]},{"text":[{"text":"1, 1, 1, 2, 2; row_number restarts whenever the date is not adjacent to the previous one, producing a streak counter directly."}],"correct":false,"tempting":[{"text":"row_number never restarts within a partition; the labels are dates, and the counter effect comes from the subtraction."}]},{"text":[{"text":"2, 2, 2, 4, 4 March; the three-day gap between 5 and 9 March advances the label by the number of missing days, so later streaks are labelled by their gap length."}],"correct":false,"tempting":[{"text":"Missing days do not advance the row number, so the label jumps by the whole gap: 9 March minus row 4 is 5 March, not 4 March."}]},{"text":[{"text":"2, 2, 2, 5, 5 March; inside a streak the date and the row number rise together, so their difference only changes where a day is skipped."}],"correct":true,"tempting":null}],"order":[0,2,1,3],"why":null},"prev":{"slug":"moving-averages","title":"Moving averages: seven rows is not seven days","minutes":8},"next":{"slug":"cohorts-and-retention","title":"Cohorts and retention: how a month of new customers behaves afterwards","minutes":10}}}