Active Users

Users active five or more consecutive days — the "islands" trick

Logins(id, login_date), Accounts(id, name). Find users who logged in on 5+ consecutive days. The earlier self-join on DATEDIFF BETWEEN 1 AND … can't enforce a run of five; the standard technique is gaps-and-islands: within one user's sorted distinct dates, subtract the row number — consecutive dates produce a constant key (an "island").

WITH d AS (                      -- one row per (user, day): a user can log in many times/day
    SELECT DISTINCT id, login_date FROM Logins
),
grp AS (
    SELECT id, login_date,
           DATE_SUB(login_date,
                    INTERVAL ROW_NUMBER() OVER (PARTITION BY id ORDER BY login_date) DAY) AS island
    FROM d
)
SELECT DISTINCT g.id, a.name
FROM grp g
JOIN Accounts a ON a.id = g.id
GROUP BY g.id, g.island, a.name
HAVING COUNT(*) >= 5;

Why date − row_number is constant on a run

Take dates 03-01, 03-02, 03-03, 03-05 with row numbers 1,2,3,4:

login_date	rn	date − rn days = island
03-01	1	02-28
03-02	2	02-28
03-03	3	02-28
03-05	4	03-01

The three consecutive days share island 02-28; the gap shifts the fourth to a new island. Grouping by island and counting gives the run length — HAVING COUNT(*) >= 5 keeps the long runs.

Takeaways

• Gaps-and-islands: date − ROW_NUMBER() is constant across consecutive dates → groups become runs.
• De-duplicate to one row per day first, or multiple same-day logins corrupt the row numbering.

---

Re-authored for correctness for this guide (the prior self-join couldn't enforce a 5-day run). Pattern: LeetCode 1454. See also: Window Functions, Running Total, Date Functions.

🎯 STRICT STANDOUT: Why / worked / when-not / failure / drills — Active Users (5-day streak)

Why this concept exists (judgment chain)

Gaps-and-islands turns consecutive-date runs into groups: within a user, date − ROW_NUMBER() is constant on an unbroken day sequence. DISTINCT per day first so multi-login days do not inflate the streak. HAVING COUNT(*)>=5 keeps islands of length ≥5.

Worked example with numbers or traced steps

Dates 03-01,03-02,03-03,03-05 → rn 1..4
date−rn: 02-28,02-28,02-28,03-01 → two islands (len 3 and 1).
Need five consecutive calendar days sharing one island key.
Without DISTINCT: two logins on 03-01 consume two rn values and break the constant key.
GROUP BY id, island HAVING COUNT(*)>=5 then join Accounts for names.

When NOT to use / named alternative

Do not use self-join DATEDIFF BETWEEN 1 AND 4 — it allows non-contiguous sets that fit a window. Skip islands if the problem is "5 logins total" not consecutive. For sparse events use match_day sequence islands (see winning streak) not calendar dates.

Failure / ops fingerprint

False positives from same-day duplicates. Timezone date boundaries splitting "days". Performance: window per user over huge Logins — index (id, login_date). Ops: define "day" in product TZ; dedupe before windows.

Hostile-panel Q&As (model answers)

Q1. Why date − row_number works?
Model answer: On consecutive days both date and rn advance by 1, difference constant; a gap advances date more than rn.

Q2. Why DISTINCT first?
Model answer: Multiple events per day would get distinct rn and shatter a true calendar streak.

Q3. How to require exactly calendar consecutiveness?
Model answer: Islands on DATE type (not timestamps) after normalizing to the business timezone date.

I just read a lesson on **Active Users** (Databases) and want to truly understand it. Explain Active Users from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Active Users** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Active Users** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Active Users** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.