// SQL
Find users who logged in on consecutive days in SQL
Every example on this page was run on 20 November 2026 in the SQL database on this page, which is close to PostgreSQL, and the output under each one is what it printed. How we test these posts.
To find users who logged in on consecutive days in SQL, keep one row per user per day, number each user’s days with ROW_NUMBER(), and subtract that number from the date. Days in an unbroken run all give the same result, so GROUP BY user_id and that result gives one row per streak, and HAVING COUNT(*) >= 3 keeps the streaks of three days or more. The pattern is known as gaps and islands.
How do I find users who logged in on 3 consecutive days in SQL?
The date minus ROW_NUMBER() trick finds users who logged in on 3 consecutive days. SELECT DISTINCT first removes repeat logins on the same day, the window function numbers each user’s days in date order, and the outer query groups the days that share a streak:
WITH logins(user_id, login_date) AS (
VALUES (1, DATE '2026-03-01'), (1, DATE '2026-03-02'), (1, DATE '2026-03-03'),
(2, DATE '2026-03-01'), (2, DATE '2026-03-03'), (2, DATE '2026-03-04'),
(3, DATE '2026-03-05'), (3, DATE '2026-03-06'), (3, DATE '2026-03-06'),
(3, DATE '2026-03-07'), (3, DATE '2026-03-08')
),
days AS (
SELECT DISTINCT user_id, login_date FROM logins
),
streaks AS (
SELECT user_id, login_date,
login_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS int) AS grp
FROM days
)
SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS days
FROM streaks
GROUP BY user_id, grp
HAVING COUNT(*) >= 3
ORDER BY user_id, streak_start;
WITH logins(user_id, login_date) AS (
VALUES (1, DATE '2026-03-01'), (1, DATE '2026-03-02'), (1, DATE '2026-03-03'),
(2, DATE '2026-03-01'), (2, DATE '2026-03-03'), (2, DATE '2026-03-04'),
(3, DATE '2026-03-05'), (3, DATE '2026-03-06'), (3, DATE '2026-03-06'),
(3, DATE '2026-03-07'), (3, DATE '2026-03-08')
),
days AS (
SELECT DISTINCT user_id, login_date FROM logins
),
streaks AS (
SELECT user_id, login_date,
login_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS int) AS grp
FROM days
)
SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS days
FROM streaks
GROUP BY user_id, grp
HAVING COUNT(*) >= 3
ORDER BY user_id, streak_start;Output
| user_id | streak_start | streak_end | days |
|---|---|---|---|
| 1 | 2026-03-01 | 2026-03-03 | 3 |
| 3 | 2026-03-05 | 2026-03-08 | 4 |
Group by login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) and keep the groups with COUNT(*) >= N. User 2 logged in three times but skipped 2 March, so their longest run is two days and they drop out. Change the 3 in HAVING for 5, 7 or any streak length. To list each user once, select DISTINCT user_id from the result.
Why does subtracting ROW_NUMBER from the date find consecutive days?
Subtracting ROW_NUMBER() from the date finds consecutive days because both go up by one per row while the run lasts. The difference stays fixed until a day is skipped: then the date jumps by two and the row number by one, so the difference moves on and a new group starts. Here is the grp column for user 2, who missed a day:
WITH logins(user_id, login_date) AS (
VALUES (2, DATE '2026-03-01'), (2, DATE '2026-03-03'), (2, DATE '2026-03-04')
)
SELECT user_id, login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn,
login_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS int) AS grp
FROM logins
ORDER BY user_id, login_date;
WITH logins(user_id, login_date) AS (
VALUES (2, DATE '2026-03-01'), (2, DATE '2026-03-03'), (2, DATE '2026-03-04')
)
SELECT user_id, login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn,
login_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS int) AS grp
FROM logins
ORDER BY user_id, login_date;Output
| user_id | login_date | rn | grp |
|---|---|---|---|
| 2 | 2026-03-01 | 1 | 2026-02-28 |
| 2 | 2026-03-03 | 2 | 2026-03-01 |
| 2 | 2026-03-04 | 3 | 2026-03-01 |
The value of grp means nothing on its own. It only has to be equal inside a run and different between runs. The CAST to int is there because PostgreSQL subtracts an integer from a date, and ROW_NUMBER() returns a bigint.
Why does my consecutive days query miss a streak?
A consecutive days query misses a streak when a user logged in twice on the same day and the rows were not made distinct first. The repeat login gets its own row number but no new date, so the difference drops by one and the run splits in two. Without DISTINCT, user 3’s four day streak from the first query breaks into two shorter runs that both contain 6 March:
WITH logins(user_id, login_date) AS (
VALUES (3, DATE '2026-03-05'), (3, DATE '2026-03-06'), (3, DATE '2026-03-06'),
(3, DATE '2026-03-07'), (3, DATE '2026-03-08')
),
streaks AS (
SELECT user_id, login_date,
login_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS int) AS grp
FROM logins
)
SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS days
FROM streaks
GROUP BY user_id, grp
ORDER BY user_id, streak_start;
WITH logins(user_id, login_date) AS (
VALUES (3, DATE '2026-03-05'), (3, DATE '2026-03-06'), (3, DATE '2026-03-06'),
(3, DATE '2026-03-07'), (3, DATE '2026-03-08')
),
streaks AS (
SELECT user_id, login_date,
login_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS int) AS grp
FROM logins
)
SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS days
FROM streaks
GROUP BY user_id, grp
ORDER BY user_id, streak_start;Output
| user_id | streak_start | streak_end | days |
|---|---|---|---|
| 3 | 2026-03-05 | 2026-03-06 | 2 |
| 3 | 2026-03-06 | 2026-03-08 | 3 |
Neither row is the real streak of four days, so a query for 4 consecutive days would miss user 3 entirely. Keep SELECT DISTINCT user_id, login_date before the window function, as the first query does. When the column holds timestamps, make the dates distinct too: SELECT DISTINCT user_id, CAST(login_at AS date) AS login_date. Two logins at 09:00 and 21:00 are still one day.
How do I find consecutive days with LAG?
LAG(login_date, 2) finds consecutive days when the streak length is fixed and small. It reads the date two rows back for the same user, and if that date is exactly two days earlier, the user logged in on three days in a row ending today. The rows still have to be distinct first:
WITH logins(user_id, login_date) AS (
VALUES (1, DATE '2026-03-01'), (1, DATE '2026-03-02'), (1, DATE '2026-03-03'),
(2, DATE '2026-03-01'), (2, DATE '2026-03-03'), (2, DATE '2026-03-04'),
(3, DATE '2026-03-05'), (3, DATE '2026-03-06'), (3, DATE '2026-03-06'),
(3, DATE '2026-03-07'), (3, DATE '2026-03-08')
),
days AS (
SELECT DISTINCT user_id, login_date FROM logins
),
checked AS (
SELECT user_id, login_date,
LAG(login_date, 2) OVER (PARTITION BY user_id ORDER BY login_date) AS two_back
FROM days
)
SELECT DISTINCT user_id
FROM checked
WHERE two_back = login_date - 2
ORDER BY user_id;
WITH logins(user_id, login_date) AS (
VALUES (1, DATE '2026-03-01'), (1, DATE '2026-03-02'), (1, DATE '2026-03-03'),
(2, DATE '2026-03-01'), (2, DATE '2026-03-03'), (2, DATE '2026-03-04'),
(3, DATE '2026-03-05'), (3, DATE '2026-03-06'), (3, DATE '2026-03-06'),
(3, DATE '2026-03-07'), (3, DATE '2026-03-08')
),
days AS (
SELECT DISTINCT user_id, login_date FROM logins
),
checked AS (
SELECT user_id, login_date,
LAG(login_date, 2) OVER (PARTITION BY user_id ORDER BY login_date) AS two_back
FROM days
)
SELECT DISTINCT user_id
FROM checked
WHERE two_back = login_date - 2
ORDER BY user_id;Output
user_id 1 3
LAG is easy to read, but the offset is the streak length minus one, and it tells you nothing about where a streak starts or how long it runs. For 7 days or a streak length that changes, use the ROW_NUMBER() version.
How do I find the longest login streak for each user?
The longest login streak for each user is the largest streak from the gaps and islands query. Count the days of every streak, then take MAX of that count per user. Users with no streak longer than one day still appear, with a longest streak of 1:
WITH logins(user_id, login_date) AS (
VALUES (1, DATE '2026-03-01'), (1, DATE '2026-03-02'), (1, DATE '2026-03-03'),
(2, DATE '2026-03-01'), (2, DATE '2026-03-03'), (2, DATE '2026-03-04'),
(3, DATE '2026-03-05'), (3, DATE '2026-03-06'), (3, DATE '2026-03-06'),
(3, DATE '2026-03-07'), (3, DATE '2026-03-08')
),
days AS (
SELECT DISTINCT user_id, login_date FROM logins
),
streaks AS (
SELECT user_id,
login_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS int) AS grp
FROM days
),
lengths AS (
SELECT user_id, COUNT(*) AS days
FROM streaks
GROUP BY user_id, grp
)
SELECT user_id, MAX(days) AS longest_streak
FROM lengths
GROUP BY user_id
ORDER BY user_id;
WITH logins(user_id, login_date) AS (
VALUES (1, DATE '2026-03-01'), (1, DATE '2026-03-02'), (1, DATE '2026-03-03'),
(2, DATE '2026-03-01'), (2, DATE '2026-03-03'), (2, DATE '2026-03-04'),
(3, DATE '2026-03-05'), (3, DATE '2026-03-06'), (3, DATE '2026-03-06'),
(3, DATE '2026-03-07'), (3, DATE '2026-03-08')
),
days AS (
SELECT DISTINCT user_id, login_date FROM logins
),
streaks AS (
SELECT user_id,
login_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS int) AS grp
FROM days
),
lengths AS (
SELECT user_id, COUNT(*) AS days
FROM streaks
GROUP BY user_id, grp
)
SELECT user_id, MAX(days) AS longest_streak
FROM lengths
GROUP BY user_id
ORDER BY user_id;Output
| user_id | longest_streak |
|---|---|
| 1 | 3 |
| 2 | 2 |
| 3 | 4 |
Does the consecutive days query work in MySQL and SQL Server?
The consecutive days query works in MySQL 8.0 and SQL Server 2012 and later, with a different way to subtract days. MySQL 8.0 writes the group as DATE_SUB(login_date, INTERVAL rn DAY), and SQL Server writes it as DATEADD(day, -rn, login_date), where rn is the ROW_NUMBER() computed in a CTE first. MySQL 5.7 has no window functions, so it needs a self join on the day before instead.
The PostgreSQL window functions reference defines row_number and lag, and the PostgreSQL date and time operators show date minus integer. The running total in SQL and top N per group posts use the same window functions over dates and groups. More questions of this shape are in the SQL interview practice guide.
Related questions
Gaps and islands is the general form of this task: grouping any rows that follow each other, such as consecutive seat numbers or unbroken order IDs, and finding the missing values between them. Retention questions such as “users active again 7 days after signing up” use the same distinct days with a self join or LAG instead.