Consecutive Login Days

The Problem

A product team wants to identify their most engaged users -- specifically, those who logged in for 3 or more consecutive days. This is a core retention metric. Mobile apps use it for "streak" features, gaming platforms use it for daily reward systems, and SaaS products use it to measure habit formation during onboarding.

The difficulty is that "consecutive" is not a simple aggregate. You cannot solve this with GROUP BY alone because you need to detect sequences within the data -- gaps between login dates matter.

The Pattern: Gaps and Islands

This is a classic "gaps and islands" problem. The technique works by assigning each row a group identifier that is the same for consecutive rows and different when there is a gap.

The trick: if you subtract a sequential row number from the date, consecutive dates produce the same result (the "island" key), while gaps produce different values.

login_date   | row_number | date - row_number
2024-01-01   | 1          | 2023-12-31
2024-01-02   | 2          | 2023-12-31  (same = consecutive)
2024-01-03   | 3          | 2023-12-31  (same = consecutive)
2024-01-05   | 4          | 2024-01-01  (different = new island)

This pattern appears in many domains:

Uptime monitoring -- finding periods of continuous availability or outage
Manufacturing -- consecutive defect-free production runs
Stock trading -- identifying winning/losing streaks
Subscription billing -- consecutive successful payment months
Sensor data -- continuous readings above a threshold (e.g., temperature alerts)
Attendance tracking -- consecutive days present or absent

Gaps and islands is one of those patterns that looks impossible until you see the row-number subtraction trick, then it becomes obvious.

The Setup

CREATE TABLE user_logins (
    user_id INT,
    login_date DATE,
    PRIMARY KEY (user_id, login_date)
);

INSERT INTO user_logins (user_id, login_date) VALUES
(1, '2024-01-01'), (1, '2024-01-02'), (1, '2024-01-03'),
(1, '2024-01-05'), (1, '2024-01-06'),
(2, '2024-01-01'), (2, '2024-01-03'), (2, '2024-01-04'),
(2, '2024-01-05'), (2, '2024-01-06'), (2, '2024-01-07'),
(3, '2024-01-10'), (3, '2024-01-11');

User 1 has a 3-day streak and a 2-day streak. User 2 has a 5-day streak. User 3 only has 2 consecutive days.

The Challenge

Find users who logged in for 3 or more consecutive days. Show the user_id, streak start date, streak end date, and streak length in days.

Test your solution here

Discuss on Discord