Write a query to find the longest streak of consecutive days a user logged into an app.

Answer

Use the "islands and gaps" technique: subtract a row number from the date to create a constant "island" identifier for each consecutive streak.

Solution

WITH login_days AS (
  SELECT DISTINCT user_id, CAST(login_time AS DATE) AS login_date
  FROM LoginEvents
),
grouped AS (
  SELECT user_id, login_date,
         DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS island
  FROM login_days
)
SELECT user_id, COUNT(*) AS streak_length
FROM grouped
GROUP BY user_id, island
ORDER BY streak_length DESC
LIMIT 1;

Step by Step

1. login_days reduces raw login events to one row per user per distinct calendar day they logged in.
2. ROW_NUMBER() numbers each user's login days sequentially (1, 2, 3...).
3. Subtracting that row number (in days) from the actual login date produces a constant value - the island - for every date that's part of one unbroken consecutive run. Two consecutive calendar dates always produce the same island value; a gap breaks the pattern and starts a new island.
4. Grouping by user_id, island and counting rows gives the length of each individual streak; ordering by that length descending gives the single longest streak overall.

Alternative: Per-User Longest Streak (Not Just the Global Maximum)

SELECT user_id, MAX(streak_length) AS longest_streak
FROM (
  SELECT user_id, island, COUNT(*) AS streak_length
  FROM grouped
  GROUP BY user_id, island
) streaks
GROUP BY user_id;

If the actual requirement is each user's own longest streak (rather than one single global winner), an extra grouping level by user_id over the per-island counts gives that instead.

Key Exam Traps

• The "islands and gaps" trick is the core concept being tested - recognizing that date - row_number produces a constant value across a consecutive run is a well-known but non-obvious pattern; explaining why it works is usually more important to an interviewer than reciting the syntax.
• Multiple logins on the same day must be deduplicated first (via DISTINCT on the date) - otherwise a user logging in 5 times in one day would be miscounted as part of the streak length calculation.
• Ties for longest streak: A plain LIMIT 1 arbitrarily picks one user if several share the same longest streak - use RANK() over the streak lengths instead if all tied users should be returned.