Detecting uninterrupted activity patterns in event logs is a classic relational database challenge often referred to as the gaps-and-islands problem. This guide outlines two efficient SQL strategies to extract accounts that have accessed a platform for five consecutive days, leveraging MySQL 8.0+ capabilities.

Environment & Data Preparation

Begin by defining a schema to track access events. The following DDL creates a table to store client identifiers and their corresponding login dates, followed by a dataset that includes valid five-day streaks and fragmented login patterns for testing.

CREATE TABLE app_login_history (
    log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    client_id VARCHAR(50),
    access_timestamp DATE
);

INSERT INTO app_login_history (client_id, access_timestamp) VALUES
('client_alpha', '2023-08-01'), ('client_alpha', '2023-08-02'), ('client_alpha', '2023-08-03'),
('client_alpha', '2023-08-04'), ('client_alpha', '2023-08-05'),
('client_beta',  '2023-08-01'), ('client_beta',  '2023-08-03'), ('client_beta',  '2023-08-04'),
('client_gamma', '2023-08-01'), ('client_gamma', '2023-08-02'), ('client_gamma', '2023-08-03'),
('client_gamma', '2023-08-04'), ('client_gamma', '2023-08-05'), ('client_gamma', '2023-08-06');

Method 1: Window Function Approach (User Identification Only)

This technique relies on sequential numbering to mathematically isolate consecutive date blocks. By partitioning records by client and assiginng a row number ordered chronologically, subtracting the sequence value from the actual date produces a constant offset to consecutive days. Grouping by this offset reveals uninterrupted streaks without pinpointing the exact start date.

WITH cleaned_logs AS (
    SELECT client_id, access_timestamp
    FROM app_login_history
    GROUP BY client_id, access_timestamp
),
sequenced_records AS (
    SELECT 
        client_id,
        access_timestamp,
        ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY access_timestamp) AS seq_val
    FROM cleaned_logs
)
SELECT DISTINCT client_id
FROM sequenced_records
GROUP BY client_id, DATE_SUB(access_timestamp, INTERVAL seq_val DAY)
HAVING COUNT(*) >= 5;

Method 2: Self-Join Technique (Identifying Streak Start Dates)

When business requirements demand the exact origin date of each consecutive period, a temporal self-join proves effective. The deduplicated access table is joined against itself using a range condition: every login date attempts to match subsequent dates falling within a five-day window. Aggregating these matches and filtering for a count of five or more isolates the precise starting timestamp of each valid streak.

WITH distinct_access AS (
    SELECT client_id, access_timestamp
    FROM app_login_history
    GROUP BY client_id, access_timestamp
)
SELECT 
    start_day.client_id,
    start_day.access_timestamp AS streak_origin
FROM distinct_access start_day
INNER JOIN distinct_access match_day
  ON start_day.client_id = match_day.client_id
  AND match_day.access_timestamp BETWEEN start_day.access_timestamp 
  AND DATE_ADD(start_day.access_timestamp, INTERVAL 4 DAY)
GROUP BY start_day.client_id, start_day.access_timestamp
HAVING COUNT(DISTINCT match_day.access_timestamp) >= 5;