HardWindow Functions High Frequency

Active User Consecutive Login Streaks (Gaps and Islands)

Frequently Asked By:MetaNetflixUber

Interactive In-Browser Workspace

Practice this problem on live schema tables in PostgreSQL 16 WASM with instant row verification and visual EXPLAIN plans.

Solve in Workbench

Problem Statement

Write a SQL query to find all active users who have logged in on at least 3 consecutive calendar days. Output user_id and the start and end dates of each qualifying streak.

Why Technical Interviewers Ask This

The "Gaps & Islands" problem separates junior SQL developers from senior database engineers. It tests whether you understand that subtracting a monotonically increasing row index from a calendar date yields a constant group key for contiguous sequences.

Common Pitfalls & Interview Traps

Not deduplicating multiple logins on the same day by the same user with DISTINCT or GROUP BY prior to windowing.

Using double LAG() or LEAD() checks — while LEAD(date, 2) can identify streaks of 3, it fails to group contiguous streaks of arbitrary length (e.g. 10 or 30 days).

Timestamp timezone mismatches when truncating timestamps to calendar dates.

Canonical Modern SQL Solution

PostgreSQL 16 / ANSI SQL

WITH UniqueDailyLogins AS (
  SELECT DISTINCT
    user_id,
    login_date::date AS login_day
  FROM user_logins
),
GroupedStreaks AS (
  SELECT
    user_id,
    login_day,
    -- Subtracting row number from the date produces an invariant group anchor
    login_day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_day))::integer AS streak_group
  FROM UniqueDailyLogins
)
SELECT
  user_id,
  MIN(login_day) AS streak_start,
  MAX(login_day) AS streak_end,
  COUNT(*) AS consecutive_days
FROM GroupedStreaks
GROUP BY user_id, streak_group
HAVING COUNT(*) >= 3
ORDER BY user_id, streak_start;

Approach Breakdown

For consecutive dates (Day 1, Day 2, Day 3), the ROW_NUMBER() sequence is (1, 2, 3). Subtracting the row number from each date yields the exact same anchor date (e.g., 2026-03-01 - 1 = 2026-02-28, 2026-03-02 - 2 = 2026-02-28). Grouping by this calculated anchor clusters each continuous island.

Edge Cases to Verbally Clarify with Your Interviewer

A user logs in 5 times on the same date (deduplicated via SELECT DISTINCT).

Streaks spanning across month boundaries or leap years (handled natively by PostgreSQL date arithmetic).

Users with non-consecutive alternating days (e.g. Mon, Wed, Fri - properly split into separate groups).

Frequently Asked Follow-Up Questions

Why not just use LEAD(login_date, 2) OVER (PARTITION BY user_id ORDER BY login_day)?

LEAD(date, 2) checks whether a third date is exactly 2 days ahead, which solves fixed 3-day windows. However, it cannot aggregate the full continuous length, streak start, and streak end of variable-length streaks (e.g. finding the longest streak of the year). The date subtraction island method handles arbitrary lengths.