Skip to content
RED SAGSkills
hard60 ptsGaps And IslandsWindow FunctionsDates

Longest Login Streak

logins holds every login with a full timestamp. A user may log in several times a day. A streak is a run of consecutive calendar days with at least one login each.

For each user, find their longest streak. If two streaks are equally long, report the earlier one.

streak_start is the first date of the streak as YYYY-MM-DD.

Return columns user_id, streak_start, streak_days. Row order does not matter.

Schema

CREATE TABLE logins (
  login_id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  login_at TEXT NOT NULL
);

Sample data

logins

login_iduser_idlogin_at
112026-03-01 08:00:00
212026-03-02 09:15:00
312026-03-02 21:40:00
412026-03-03 07:30:00
512026-03-05 10:00:00
622026-03-01 12:00:00
722026-03-03 12:00:00
822026-03-04 12:00:00
932026-03-10 23:59:59
Hint 1

Remove repeat logins on the same day first: SELECT DISTINCT user_id, date(login_at).

Hint 2

Within a run of consecutive days, the day number minus ROW_NUMBER() stays the same. Use that difference as an island id.

Hint 3

Rank the islands per user by length, then by start date.

Discussion 0

Join free to post in the discussion.

Good practice for: Data Analyst, Data Scientist, Product Manager, Data Engineer

SQL (SQLite)

Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.