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…