Longest Daily Login Streak per Player

Hard
191.

Longest Daily Login Streak per Player

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

ColumnType
event_idint
player_idint
login_atdatetime

Primary key: event_id.

One row per successful sign-in (IST).

Example

The rows must come back in the order shown.

LoginEvent

event_idplayer_idlogin_at
152025-03-01 10:00:00
252025-03-02 23:59:00
352025-03-03 00:01:00
452025-03-03 21:00:00
552025-03-05 08:00:00
662025-02-27 19:00:00
762025-02-28 19:00:00
862025-03-01 19:00:00
962025-03-02 19:00:00
1072025-03-04 12:00:00

Output

player_idlongest_streak_days
64
53
71
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
event_idint
player_idint
login_atdatetime

Primary key: event_id.

One row per successful sign-in (IST).

Examples

Example 1

LoginEvent

event_idplayer_idlogin_at
152025-03-01 10:00:00
252025-03-02 23:59:00
352025-03-03 00:01:00
452025-03-03 21:00:00
552025-03-05 08:00:00
662025-02-27 19:00:00
762025-02-28 19:00:00
862025-03-01 19:00:00
962025-03-02 19:00:00
1072025-03-04 12:00:00

Output

player_idlongest_streak_days
64
53
71

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_id

Another 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_id

Another 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 →