Find users who logged in on 3 or more consecutive days (gaps-and-islands).

TL;DR: Dedupe to one row per user-day, then subtract ROW_NUMBER (ordered by date) from the date: that difference is constant within each consecutive run, so it becomes the GROUP BY key per island. Filter islands with COUNT(*) >= 3.

How to approach it

Name the pattern out loud, "this is gaps-and-islands", then explain the core trick before typing: for consecutive values, (value − row_number) is constant within each run. Clarify upfront: multiple logins per day (dedupe to one row per user-day first) and what to return (users only, or the streaks themselves).

A strong answer

WITH daily AS (                      -- one row per user per day
  SELECT DISTINCT user_id, CAST(login_ts AS DATE) AS login_date
  FROM logins
),
grouped AS (
  SELECT
    user_id,
    login_date,
    DATEADD(day,                      -- ANSI: login_date - rn * INTERVAL '1 day'
      -ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date),
      login_date) AS island_key
  FROM daily
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*)        AS streak_days
FROM grouped
GROUP BY user_id, island_key
HAVING COUNT(*) >= 3;

Trace it by hand on one user's dates Jan 1, 2, 3, 5, 6:

The constant identifies each island, and the gap at Jan 4 shifts the constant by one (the row_number keeps incrementing while the date jumps two). Island A has three days and qualifies; island B has two and drops out. Then GROUP BY the island and filter on size. Wrap with SELECT DISTINCT user_id if only users are wanted.

Two refinements that read senior:

• The DISTINCT matters. Duplicate user-days give two rows the same date but different row_numbers, fracturing the arithmetic. This is the most common way correct-looking solutions fail, and it is stark when you actually run it: give one user logins on Jan 1, Jan 2, Jan 2, Jan 3 and the query without the dedupe returns two islands of two days each (Jan 1-2 and Jan 2-3, executed in sqlite), so the user with an obvious three-day streak fails the >= 3 filter. With the DISTINCT, the same data returns the single correct island, Jan 1 to Jan 3, three days. The mechanism: the duplicate Jan 2 consumes a row_number, so from that point every date-minus-rn value in the run shifts by one and the island key splits. Note the failure direction, because it is the sneaky part: duplicates hide streaks rather than inventing them, so the bug reads as "engagement is lower than expected" and gets attributed to users instead of to SQL.
• Alternative with LAG: compute LAG(login_date) per user, flag DATEDIFF(day, prev, curr) > 1 as a new-streak marker, then a running SUM of flags creates the island id. Same result, more steps, but generalizes to "consecutive" definitions other than +1 day (e.g., sessions within 30 minutes), worth mentioning as the general form.

What interviewers probe next

• "Longest streak per user", MAX(streak_days) over the grouped CTE.
• "Current streak as of today", filter islands where streak_end = CURRENT_DATE (or yesterday, define it).
• "3 consecutive weeks / visits", swap the date arithmetic for ROW_NUMBER-minus-ROW_NUMBER on ordinal positions, or the LAG-flag form.
• "Scale: 500M logins/day", dedupe early (DISTINCT cuts volume), partition pruning on date, the window is one shuffle on user_id; user skew is rare but mention bots.

Common mistakes

• Self-joining ON date = date + 1 chains, works for exactly 3 days, explodes for "N or more."
• Skipping the user-day dedupe.
• Mixing timestamp and date types so subtraction misbehaves.
• Being unable to explain why date-minus-rn works, interviewers always ask; if you can't derive it on a 5-row example, they assume it's memorized.