Longest Daily Usage Streak per User

Hard
419.

Longest Daily Usage Streak per User

The app shows each user their best streak: the longest run of consecutive calendar days on which they used the product at least once. Several uses on one day are one day, and a streak may cross a month boundary.

Return user_id, longest_streak (in days) and streak_start (the first day of that streak) for every user with usage. If a user has two streaks of the same longest length, report the earliest one. Order by user_id.

Tables

Table: usage_log

ColumnType
log_idint
user_idint
used_atdatetime

Primary key: log_id.

One row per use of the app.

Example

The rows must come back in the order shown.

usage_log

log_iduser_idused_at
112024-06-01 09:00:00
212024-06-02 08:30:00
312024-06-02 21:15:00
412024-06-03 07:45:00
512024-06-05 10:00:00
612024-06-06 10:00:00
712024-06-07 23:59:00
822024-06-10 12:00:00
932024-06-28 18:00:00
1032024-06-29 18:00:00
3 more rows

Output

user_idlongest_streakstreak_start
132024-06-01
212024-06-10
342024-06-28
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 Usage Streak per User — SQL Problem

The app shows each user their best streak: the longest run of consecutive calendar days on which they used the product at least once.

  • Difficulty: Hard
  • Topics: Window Functions, Dates
  • Dialect: MySQL
  • Problem: #419

Problem statement

The app shows each user their best streak: the longest run of consecutive calendar days on which they used the product at least once. Several uses on one day are one day, and a streak may cross a month boundary.

Return user_id, longest_streak (in days) and streak_start (the first day of that streak) for every user with usage. If a user has two streaks of the same longest length, report the earliest one. Order by user_id.

Tables

Table: usage_log

ColumnType
log_idint
user_idint
used_atdatetime

Primary key: log_id.

One row per use of the app.

Examples

Example 1

usage_log

log_iduser_idused_at
112024-06-01 09:00:00
212024-06-02 08:30:00
312024-06-02 21:15:00
412024-06-03 07:45:00
512024-06-05 10:00:00
612024-06-06 10:00:00
712024-06-07 23:59:00
822024-06-10 12:00:00
932024-06-28 18:00:00
1032024-06-29 18:00:00
1132024-06-30 18:00:00
1232024-07-01 00:05:00
1332024-07-03 18:00:00

Output

user_idlongest_streakstreak_start
132024-06-01
212024-06-10
342024-06-28

How to solve Longest Daily Usage Streak per User

A classic gaps and islands problem. Deduplicate to distinct (user, day) pairs — two uses on 2 June are one day — then number each user's days in order with ROW_NUMBER(). Inside a run of consecutive days both the date and the row number rise by exactly one, so DATEDIFF(day, '2000-01-01') - row_number is the same for every day of the run and changes at every gap. That value is the island's key; it is computed on integer day numbers rather than by subtracting an interval from a date, which keeps it simple in every engine.

Grouping by (user, key) gives each streak's first day and length. Because the key comes from day arithmetic, a streak crossing a month boundary — 28 June to 1 July for user 3 — stays one island.

Choosing one streak per user with ties is a top-1-per-group: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY len DESC, streak_start) makes the longest come first and the earliest win a tie (user 1 has two three-day streaks, and 1 June wins).

The alternative marks island starts with LAG — a day whose previous day is not exactly one day earlier — numbers islands with a running sum, and keeps the streak no other streak beats via NOT EXISTS. Both are a sort per user.

Reference solution (MySQL)

WITH days AS (
  SELECT DISTINCT user_id, DATE(used_at) AS day FROM usage_log
), islands AS (
  SELECT user_id, day,
         DATEDIFF(day, '2000-01-01') - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) AS grp
  FROM days
), streaks AS (
  SELECT user_id, MIN(day) AS streak_start, COUNT(*) AS len
  FROM islands
  GROUP BY user_id, grp
), ranked AS (
  SELECT user_id, streak_start, len,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY len DESC, streak_start) AS rn
  FROM streaks
)
SELECT user_id, len AS longest_streak, streak_start
FROM ranked
WHERE rn = 1
ORDER BY user_id

Another way

WITH days AS (
  SELECT DISTINCT user_id, DATE(used_at) AS day FROM usage_log
), marked AS (
  SELECT user_id, day,
         CASE WHEN DATEDIFF(day, LAG(day) OVER (PARTITION BY user_id ORDER BY day)) = 1 THEN 0 ELSE 1 END AS is_start
  FROM days
), numbered AS (
  SELECT user_id, day, SUM(is_start) OVER (PARTITION BY user_id ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS island
  FROM marked
), streaks AS (
  SELECT user_id, island, MIN(day) AS streak_start, COUNT(*) AS len FROM numbered GROUP BY user_id, island
)
SELECT s.user_id, s.len AS longest_streak, s.streak_start
FROM streaks s
WHERE NOT EXISTS (SELECT 1 FROM streaks t WHERE t.user_id = s.user_id
                  AND (t.len > s.len OR (t.len = s.len AND t.streak_start < s.streak_start)))
ORDER BY s.user_id

← Monthly Signup Cohort Retention · Month-End MRR From Subscription Periods →