SQL · hard · Reported at Google

Find users who logged in 3 or more consecutive days.

Asked in Data Engineer interviews, in the SQL round.

Short answer

Use ROW_NUMBER and date - row_number as a group key, then count per group.

Sample data

These tables are loaded into an in-browser engine on this page, so you can run your own answer against them. No account needed.

logins

user_idlogin_date
12026-04-01
12026-04-02
12026-04-03
12026-04-04
22026-04-01
22026-04-03
22026-04-04
32026-04-02
32026-04-05
32026-04-06
32026-04-07
42026-04-01

How to answer it

Definition

Find runs of consecutive calendar days per user, then keep the users whose run reaches three days. The standard approach is the gaps-and-islands pattern.

The trick

Number each user's distinct login days in order. Within a run of consecutive days, date - row_number is constant, because both increase by one per step. That constant is a group key for the run.

login_date   rn   date - rn
2025-03-01    1   2025-02-28   <- run A
2025-03-02    2   2025-02-28   <- run A
2025-03-03    3   2025-02-28   <- run A
2025-03-09    4   2025-03-05   <- run B

Example

WITH days AS (
  SELECT DISTINCT user_id, CAST(login_date AS DATE) AS login_date
  FROM   logins
  WHERE user_id IS NOT NULL AND login_date IS NOT NULL
),
grouped AS (
  SELECT user_id,
         login_date,
         login_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS INT) AS grp
  FROM   days
)
SELECT user_id, COUNT(*) AS streak, MIN(login_date) AS started
FROM   grouped
GROUP BY user_id, grp
HAVING COUNT(*) >= 3
ORDER BY user_id, started;

Output

user_id | streak |  started
--------+--------+------------
      1 |      4 | 2026-04-01
      3 |      3 | 2026-04-05

This PostgreSQL/DuckDB query runs against the logins sample table above. User 1 has four consecutive dates. User 2 has three login dates but a gap after April 1, so does not qualify. User 3's April 2 login is a separate island from the qualifying April 5-7 streak. User 4 has only one day.

Return each qualifying user only once

The result above deliberately exposes each streak so you can inspect it. If the requested output is a list of users, use the same CTEs and replace the final SELECT with:

SELECT DISTINCT user_id
FROM (
  SELECT user_id, grp
  FROM grouped
  GROUP BY user_id, grp
  HAVING COUNT(*) >= 3
) AS qualifying_streaks
ORDER BY user_id;

The sample returns users 1 and 3. A user with two qualifying streaks still appears only once.

Test the edge cases

Key points

FAQ

The 30-second answer

This is gaps and islands. I take the distinct login days per user, number them with ROW_NUMBER ordered by date, and subtract the row number from the date. Within a run of consecutive days that difference is constant, because both sides step by one, so it works as a group key for the streak.
Then I group by user and that key, count the rows, and keep groups of three or more. Two things I would say without being asked: the distinct is load bearing, because two logins on one day would consume two row numbers and break the arithmetic, and the column has to be a date rather than a timestamp for the same reason.

In the interview

Practice this for real