How do you approach SQL: Three Consecutive Logins (LeetCode 180 variant)?

Gaps-and-islands trick: subtract a running ROW_NUMBER from the date; consecutive days produce the same offset, so a GROUP BY (user_id, date - rn) collapses each streak into one group.

What are common mistakes on SQL: Three Consecutive Logins (LeetCode 180 variant)?

Filtering on an aggregate in WHERE rather than HAVING. An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders. COUNT(*) versus COUNT(column). The second skips nulls, and that difference is often the entire question.

gitGood.dev

← Back to problems

SQL: Three Consecutive Logins (LeetCode 180 variant)

HardDatabases

Description

> This problem is paper-only - see explanation for the canonical SQL solution. (Streak / island problem - the classic gaps-and-islands pattern.) Given `Logins(user_id, login_date)` (one row per (user, date), pre-sorted), return user_ids who have logged in on **at least three consecutive calendar days** somewhere in the data. Sort ascending. **For the auto-grader:** implement `usersWithStreak(logins)`.

Example 1

Input: logins = [{user_id:1,login_date:"2026-05-01"},{user_id:1,login_date:"2026-05-02"},{user_id:1,login_date:"2026-05-03"},{user_id:2,login_date:"2026-05-01"},{user_id:2,login_date:"2026-05-03"}]

Output: [1]

Constraints

- 0 <= logins.length <= 10^4

SQL: Three Consecutive Logins (LeetCode 180 variant): approach, complexity and pitfalls

A hard sql problem, graded against 5 test cases (3 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

More SQLproblems →

If you are stuck

Show the 2-step hint ladder

Gaps-and-islands trick: subtract a running ROW_NUMBER from the date; consecutive days produce the same offset, so a GROUP BY (user_id, date - rn) collapses each streak into one group.
Then COUNT(*) per group and keep groups >= 3.

The optimal approach

Show the approach: Canonical SQL (Postgres):

```sql

WITH numbered AS (

SELECT user_id, login_date,

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn

FROM Logins

groups AS (

SELECT user_id, COUNT(*) AS streak_len

FROM numbered

GROUP BY user_id, login_date - rn * INTERVAL '1 day'

)

SELECT DISTINCT user_id

FROM groups

WHERE streak_len >= 3

ORDER BY user_id;

```

Why this works: if dates are 2026-05-01, 02, 03 their row numbers within the partition are 1, 2, 3, so date - rn days is constant (2026-04-30 in all three cases). A gap (skipped day) bumps the offset, splitting a new group. Counting rows per group gives streak length.

This is the canonical SQL pattern for "find consecutive runs". Variants:

- "Consecutive identical values" - same trick on a non-date column.

- "At least N in a row" - filter on COUNT(*) >= N.

- "Longest streak per user" - MAX of streak_len per user.

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

What the tests check

Read off this problem's own test suite, so these are the cases a submission actually has to survive.

Empty input. At least one case passes an empty collection. Make sure your loop bounds and any "first element" read survive a length of zero.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.