Consecutive Login Streak Detector (Gaps and Islands)

What you will learn

• Understand the mathematical principles of the SQL gaps-and-islands problem
• Generate sequential rank integers using ROW_NUMBER() partitioned by user and ordered by date
• Calculate constant island grouping keys by subtracting integer row numbers from calendar dates
• Aggregate start dates, end dates, and streak lengths for user engagement gamification

What you need first

SELECT DATE '2024-03-02' - 2 * INTERVAL '1 day' AS anchor_date;

anchor_date
-----------
2024-02-29

SELECT COUNT(*) AS streak_length FROM (VALUES (1), (2), (3)) t;

streak_length
-------------
3

Architecture and data flow

The data

User daily login ledger recording dates of platform engagement.

CREATE OR REPLACE TABLE user_logins (
  login_id INT,
  user_id INT,
  login_date DATE
);
INSERT INTO user_logins VALUES
  (1, 101, DATE '2024-03-01'),
  (2, 101, DATE '2024-03-02'),
  (3, 101, DATE '2024-03-03'),
  (4, 101, DATE '2024-03-05'),
  (5, 101, DATE '2024-03-06'),
  (6, 102, DATE '2024-03-01'),
  (7, 102, DATE '2024-03-02');

Step by step

Read each step, run its code, then check your output against the one shown.

1. 01

   Inspect chronological user login activity

   We begin by querying the raw user_logins table ordered by user_id and login_date. User 101 logged in on March 1, 2, and 3, skipped March 4, and returned on March 5 and 6. User 102 logged in on March 1 and 2. In gamification analytics, inspecting the baseline activity confirms that users have distinct login dates and that date gaps are clearly visible before applying window calculations. Examining raw login logs verifies that users engage across multiple days.

   SQLStep 1Copy

   SELECT login_id, user_id, login_date FROM user_logins ORDER BY user_id, login_date;

   Show outputGuess what it prints first

   login_id | user_id | login_date
   ---------+---------+-----------
   1        | 101     | 2024-03-01
   2        | 101     | 2024-03-02
   3        | 101     | 2024-03-03
   4        | 101     | 2024-03-05
   5        | 101     | 2024-03-06
   6        | 102     | 2024-03-01
   7        | 102     | 2024-03-02

   Common mistake

   Assuming login_date is always unique per user without performing an initial DISTINCT deduplication step.

   How you will know

   You will see 7 login records ordered cleanly by user and login date.

2. 02

   Attempt naive date difference span

   Wrong turn first: this step shows the mistake before the fix

   Here is the naive approach: computing streak length by simply subtracting MIN(login_date) from MAX(login_date) per user. For user 101, March 6 minus March 1 plus 1 yields 6 days, completely ignoring the fact that the user was inactive on March 4. This naive query creates phantom streaks across inactive intervals, leading to erroneous reward allocations. Calculating the span between minimum and maximum dates completely ignores skipped days, resulting in inflated streak counts that drain promotional loyalty budgets prematurely. In production user analytics, relying on crude min-max span calculations misrepresents true customer engagement and compromises the integrity of gamification leaderboards.

   SQLStep 2Copy

   SELECT user_id, MIN(login_date) AS first_login, MAX(login_date) AS last_login, (MAX(login_date) - MIN(login_date)) + 1 AS naive_streak_span FROM user_logins GROUP BY user_id ORDER BY user_id;

   Show outputGuess what it prints first

   user_id | first_login | last_login | naive_streak_span
   --------+-------------+------------+------------------
   101     | 2024-03-01  | 2024-03-06 | 6
   102     | 2024-03-01  | 2024-03-02 | 2

   Common mistake

   Calculating total date span between min and max dates instead of detecting contiguous unbroken intervals.

   How you will know

   Notice that naive date span reports 6 days for user 101, masking the missing login on March 4.

3. 03

   Assign chronological row numbers per user

   We apply the ROW_NUMBER() window function partitioned by user_id and ordered by login_date. This generates a continuous integer sequence (1, 2, 3, 4, 5) for user 101. On consecutive days, both the calendar date and the row number advance by exactly 1. When a calendar day is skipped (March 4), the date advances by 2 while the row number advances by only 1, creating a mathematical divergence. This divergence between calendar days and sequential row numbers is the fundamental mechanism that allows SQL queries to detect gaps in temporal activity.

   SQLStep 3Copy

   SELECT
     user_id,
     login_date,
     ROW_NUMBER() OVER (
       PARTITION BY user_id
       ORDER BY login_date
     ) AS seq_num
   FROM user_logins
   ORDER BY user_id, login_date;

   Show outputGuess what it prints first

   user_id | login_date | seq_num
   --------+------------+--------
   101     | 2024-03-01 | 1
   101     | 2024-03-02 | 2
   101     | 2024-03-03 | 3
   101     | 2024-03-05 | 4
   101     | 2024-03-06 | 5
   102     | 2024-03-01 | 1
   102     | 2024-03-02 | 2

   Common mistake

   Forgetting PARTITION BY user_id, which causes row numbers to increment across multiple users and destroys user isolation.

   How you will know

   Output shows seq_num incrementing from 1 to 5 for user 101 and 1 to 2 for user 102.

4. 04

   Derive constant island grouping anchor date

   We compute the island grouping key by subtracting seq_num days from login_date. For user 101: March 1 minus 1 day = 2024-02-29; March 2 minus 2 days = 2024-02-29; March 3 minus 3 days = 2024-02-29. All three consecutive days share the exact same anchor date. On March 5 (seq_num 4), March 5 minus 4 days = 2024-03-01, creating a brand new island group for the second streak. Subtracting row numbers from calendar dates produces identical anchor dates across contiguous sequences, neatly partitioning discontinuous activity into discrete island identifiers.

   SQLStep 4Copy

   SELECT
     user_id,
     login_date,
     login_date - CAST(ROW_NUMBER() OVER (
       PARTITION BY user_id
       ORDER BY login_date
     ) AS INT) * INTERVAL '1 day' AS island_group
   FROM user_logins
   ORDER BY user_id, login_date;

   Show outputGuess what it prints first

   user_id | login_date | island_group
   --------+------------+--------------------
   101     | 2024-03-01 | 2024-02-29 00:00:00
   101     | 2024-03-02 | 2024-02-29 00:00:00
   101     | 2024-03-03 | 2024-02-29 00:00:00
   101     | 2024-03-05 | 2024-03-01 00:00:00
   101     | 2024-03-06 | 2024-03-01 00:00:00
   102     | 2024-03-01 | 2024-02-29 00:00:00
   102     | 2024-03-02 | 2024-02-29 00:00:00

   Common mistake

   Using integer subtraction directly without multiplying by INTERVAL '1 day' in engines that require explicit date intervals.

   How you will know

   March 1-3 share island_group 2024-02-29; March 5-6 share island_group 2024-03-01.

5. 05

   Synthesize streak summary with start, end, and duration

   In the final query, we group by user_id and island_group to summarize every discrete streak. We compute streak_start with MIN(login_date), streak_end with MAX(login_date), and streak_length using COUNT(*). The result correctly identifies 3 distinct streaks: user 101 has a 3-day streak and a 2-day streak, while user 102 has a 2-day streak. The query delivers the exact data shape required by product gamification engines. Aggregating by island group computes exact streak durations.

   SQLStep 5Copy

   WITH island_calc AS (
     SELECT
       user_id,
       login_date,
       login_date - CAST(ROW_NUMBER() OVER (
         PARTITION BY user_id
         ORDER BY login_date
       ) AS INT) * INTERVAL '1 day' AS island_group
     FROM user_logins
   )
   SELECT
     user_id,
     MIN(login_date) AS streak_start,
     MAX(login_date) AS streak_end,
     CAST(COUNT(*) AS INT) AS streak_length
   FROM island_calc
   GROUP BY user_id, island_group
   ORDER BY user_id, streak_start;

   Show outputGuess what it prints first

   user_id | streak_start | streak_end | streak_length
   --------+--------------+------------+--------------
   101     | 2024-03-01   | 2024-03-03 | 3
   101     | 2024-03-05   | 2024-03-06 | 2
   102     | 2024-03-01   | 2024-03-02 | 2

   Common mistake

   Grouping only by user_id without island_group, which merges separate streaks back into a single total day count.

   How you will know

   The output provides 3 discrete streaks with exact start dates, end dates, and streak lengths.

Build it yourself

Write your solution below, run it in your browser, and pass every check.

What the result means

Interpretation

The gaps-and-islands analysis accurately disproves the assumption that user 101 maintained a continuous 5-day streak. By subtracting sequential row numbers from login dates, the model isolated two separate activity islands: a 3-day streak ending March 3, and a new 2-day streak starting March 5. This ensures reward criteria are strictly enforced and prevents premature voucher disbursement.

The message you would send

Hi Gamification Product Team, the SQL gaps-and-islands streak detection logic is complete. The model reliably breaks streaks whenever a calendar day is missed, preventing improper reward distributions. Integration with the rewards engine will safeguard our promotional budget.

Put it on your CV

Resume or LinkedIn bullet

Implemented the classic SQL gaps-and-islands algorithm to detect consecutive daily activity streaks and breaks in DuckDB.

README for GitHub

# Consecutive Login Streak Detector (Gaps and Islands)

An advanced SQL pipeline solving the classic gaps-and-islands problem to detect unbroken daily activity streaks.

## Features
- Chronological row numbering partitioned by user entity
- Constant island grouping derivation via date arithmetic (date - row_num * interval)
- Exact unbroken streak length, start date, and end date aggregation
- Scalable pattern for gamification, retention streaks, and sensor anomaly detection

Keep going

• LessonSQL window functions
• Case studyHomeHelp Repeat Booking Lag
• Case studyDo New Members Come Back?