SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

Interview Drills · Gaps and islands

35. Longest login streak

Concept: Gaps and islandsshow

Consecutive dates share a trick: subtract each row's ROW_NUMBER (per user, in date order) from its date and every day in the same streak lands on the same 'anchor' date. GROUP BY that anchor to measure each streak, then take the MAX per user.

Example (different data):

WITH g AS (
  SELECT day,
    day - (ROW_NUMBER() OVER (ORDER BY day))::int AS anchor
  FROM active_days
)
SELECT MIN(day) AS start, COUNT(*) AS length
FROM g GROUP BY anchor;

Your task

A hard FAANG favourite: for each user, how long was their longest run of consecutive login days?

Return user_id and longest_streak (most consecutive days logged in) for every user. Longest first, ties by user_id.

Starting Postgres in your browser…

Checking your access…