Game Play Analysis IV

Fraction of players who came back the very next day

Activity(player_id, device_id, event_date, games_played). Report the fraction of players who logged in again on the day immediately after their first login, rounded to 2 decimals. The earlier headline solution used an EXISTS subquery that checked the wrong relationship; the correct approach pins each player's first login, then asks whether first + 1 day is also an activity date.

SELECT ROUND(
         COUNT(DISTINCT a.player_id)
         / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction
FROM Activity a
WHERE (a.player_id, DATE_SUB(a.event_date, INTERVAL 1 DAY)) IN (
        SELECT player_id, MIN(event_date)      -- (player, their first-login date)
        FROM Activity
        GROUP BY player_id
);

How it works, traced

1. The inner query gives each player's first login: (player_id, MIN(event_date)).
2. A row a qualifies when (a.player_id, a.event_date - 1 day) matches a first-login pair — i.e. a.event_date is exactly the day after that player's first login.
3. Numerator = distinct players with such a row; denominator = total distinct players; ROUND(…, 2).

For player 1 first logging in on 2016-03-01: they count only if they also have an activity row on 2016-03-02. Logging in on 2016-03-03 but not the 2nd does not count.

Pitfalls

• "Next day" is the day after the first login, not "any two consecutive logins" — anchor on MIN(event_date).
• Integer division: in MySQL count/count is decimal so ROUND works; in Postgres cast to avoid integer division.

Takeaways

• Anchor on each player's first login (MIN(event_date)), then test for first + 1 day.
• The row-value IN ((id, date)) form is a clean way to match a pair against a grouped set.

---

Re-authored for correctness for this guide (the prior EXISTS solution tested the wrong condition). Pattern: LeetCode 550. See also: Date Functions, GROUP BY, Subqueries.

🎯 STRICT STANDOUT: Why / worked / when-not / failure / drills — Game Play Analysis IV

Why this concept exists (judgment chain)

Day-2 retention anchors on each player’s first login, not any consecutive pair. The pair IN (player, first_date) pattern tests whether first+1 day exists — a reusable retention primitive.

Worked example with numbers or traced steps

Activity player 1: 2016-03-01, 2016-03-02, 2016-03-05
First = 03-01; day-2 = 03-02 present → player counts in numerator.
Player only 03-01 and 03-05 → does NOT count.
fraction = ROUND( day2_players / total_distinct_players , 2 )
Pair form: (player_id, event_date - 1 day) IN (player_id, MIN(event_date))
Postgres: cast counts to numeric to avoid integer division.

When NOT to use / named alternative

Do not count “any two consecutive days” as D1 retention. Do not use EXISTS that only checks a later login without anchoring MIN. Prefer window (event_date - first_date = 1) for readability on large scans if indexed well.

Failure / ops fingerprint

Fingerprint: retention 1.0 because any return login counted; integer division → 0.00 in PG. Ops: index (player_id, event_date); materialize first-login daily for large fact tables.

Hostile-panel drills (defend the decision)

Q1. What is the retention definition here?
Model answer: Logged in on the calendar day immediately after the player’s first login day.

Q2. Why MIN(event_date)?
Model answer: Anchors “first login”; without it consecutive pairs later in the life cycle inflate retention.

Q3. Integer division pitfall?
Model answer: In Postgres int/int truncates; cast to numeric/float before ROUND.

I just read a lesson on **Game Play Analysis IV** (Databases) and want to truly understand it. Explain Game Play Analysis IV 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 **Game Play Analysis IV** 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 **Game Play Analysis IV** 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 **Game Play Analysis IV** 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.