Longest Daily Login Streak per Player — SQL Problem
The login-reward calendar pays out for consecutive days played.
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #191
Problem statement
The login-reward calendar pays out for consecutive days played. A player's streak is a run of consecutive calendar days on each of which they logged in at least once; several logins on one day count as one day, and a single missed day ends the streak.
For every player with at least one login, return player_id and longest_streak_days — the length of their longest streak. Order by longest_streak_days descending, then player_id.
Tables
Table: LoginEvent
| Column | Type |
|---|---|
| event_id | int |
| player_id | int |
| login_at | datetime |
Primary key: event_id.
One row per successful sign-in (IST).
Examples
Example 1
LoginEvent
| event_id | player_id | login_at |
|---|---|---|
| 1 | 5 | 2025-03-01 10:00:00 |
| 2 | 5 | 2025-03-02 23:59:00 |
| 3 | 5 | 2025-03-03 00:01:00 |
| 4 | 5 | 2025-03-03 21:00:00 |
| 5 | 5 | 2025-03-05 08:00:00 |
| 6 | 6 | 2025-02-27 19:00:00 |
| 7 | 6 | 2025-02-28 19:00:00 |
| 8 | 6 | 2025-03-01 19:00:00 |
| 9 | 6 | 2025-03-02 19:00:00 |
| 10 | 7 | 2025-03-04 12:00:00 |
Output
| player_id | longest_streak_days |
|---|---|
| 6 | 4 |
| 5 | 3 |
| 7 | 1 |
How to solve Longest Daily Login Streak per Player
This is gaps and islands on dates. Start by collapsing the log to one row per player per calendar day with SELECT DISTINCT player_id, DATE(login_at) — without that, a second login on the same day would look like an extra day in the run.
Then number each player's days with ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY login_day). On a run of consecutive dates the date advances by one day exactly when the row number advances by one, so DATEDIFF(login_day, fixed_date) - row_number is the same for the whole run and jumps whenever a day is missed. That value labels the island; COUNT(*) per (player, island) is the streak length, and MAX per player is the answer. Player 5 has 1–3 March (3 days, the 23:59 and 00:01 logins bridging two days) and 5 March alone.
Equivalently, flag each day that does not follow the previous one (LAG + DATEDIFF = 1), turn the flags into streak numbers with a running SUM, and count. A set-based version pairs each streak start (no login the day before) with the first end at or after it (no login the day after). The window versions sort each player's days once.
Reference solution (MySQL)
WITH days AS (
SELECT DISTINCT player_id, DATE(login_at) AS login_day
FROM LoginEvent
), keyed AS (
SELECT player_id, login_day,
DATEDIFF(login_day, '2000-01-01') - ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY login_day) AS island
FROM days
), streaks AS (
SELECT player_id, island, COUNT(*) AS streak_len
FROM keyed
GROUP BY player_id, island
)
SELECT player_id, MAX(streak_len) AS longest_streak_days
FROM streaks
GROUP BY player_id
ORDER BY longest_streak_days DESC, player_idAnother way
WITH days AS (SELECT DISTINCT player_id, LEFT(login_at, 10) AS login_day FROM LoginEvent),
flagged AS (
SELECT player_id, login_day,
CASE WHEN DATEDIFF(login_day, LAG(login_day) OVER (PARTITION BY player_id ORDER BY login_day)) = 1 THEN 0 ELSE 1 END AS starts
FROM days
), numbered AS (
SELECT player_id, login_day,
SUM(starts) OVER (PARTITION BY player_id ORDER BY login_day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS streak_no
FROM flagged
)
SELECT player_id, MAX(cnt) AS longest_streak_days
FROM (SELECT player_id, streak_no, COUNT(*) AS cnt FROM numbered GROUP BY player_id, streak_no) s
GROUP BY player_id
ORDER BY longest_streak_days DESC, player_idAnother way
WITH days AS (SELECT DISTINCT player_id, DATE(login_at) AS login_day FROM LoginEvent)
SELECT player_id, MAX(len) AS longest_streak_days FROM (
SELECT s.player_id, s.login_day, DATEDIFF(MIN(e.login_day), s.login_day) + 1 AS len
FROM days s
JOIN days e ON e.player_id = s.player_id AND e.login_day >= s.login_day
WHERE NOT EXISTS (SELECT 1 FROM days n WHERE n.player_id = e.player_id AND n.login_day = DATE_ADD(e.login_day, INTERVAL 1 DAY))
AND NOT EXISTS (SELECT 1 FROM days b WHERE b.player_id = s.player_id AND b.login_day = DATE_SUB(s.login_day, INTERVAL 1 DAY))
GROUP BY s.player_id, s.login_day
) t
GROUP BY player_id
ORDER BY longest_streak_days DESC, player_id← Each Player's First Store Purchase After Sign-up · Day-1 and Day-7 Retention by Signup Week →