Three Consecutive Logins

Problem

Schema: Login(user_id INT, login_date DATE). Each (user_id, login_date) is unique. Return the user_ids who logged in on three or more consecutive calendar days (e.g. Jan 5, 6, 7).

Constraints

• Multiple consecutive runs per user count as one match.

Approach — Core Patterns

This is a Core Patterns problem. The idea: pick the data structure best matched to the constraints and solve it in one clean pass. Work through the reference code below line by line, then re-derive it yourself in the editor — that's how the pattern sticks.

Complexity: see the walkthrough below.

Solution code

SQL

WITH numbered AS (
  SELECT user_id, login_date,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
  FROM Login
),
streaks AS (
  SELECT user_id, login_date,
         julianday(login_date) - rn AS streak_key
  FROM numbered
),
lengths AS (
  SELECT user_id, COUNT(*) AS streak_len
  FROM streaks
  GROUP BY user_id, streak_key
)
SELECT DISTINCT user_id
FROM lengths
WHERE streak_len >= 3;

Practice it

Reading a solution isn't the same as being able to write it under pressure. Open this problem in the in-browser editor, hide the solution, and solve it from scratch — your code runs against real test cases instantly.