Game Play Analysis I

GROUP BY player_id tells the engine to shred the Activity rows into one bucket per distinct player_id, and MIN(event_date) then collapses each bucket to its single smallest date — so the whole query is "partition the rows, then reduce each partition to one value."

The task

Table Activity has primary key (player_id, event_date) — one row per player per day they logged in. We want each player's first login date (the earliest event_date).

SELECT player_id,
       MIN(event_date) AS first_login
FROM Activity
GROUP BY player_id;

This is the entire, correct solution. The interesting part is how the engine produces it, and the ways the obvious variants silently break.

Worked trace on the real sample

The sample Activity table:

Step 1 — partition by player_id. The engine does not first produce a flat list of (player_id, event_date) rows; the GROUP BY is logically applied before the SELECT expressions are evaluated. After grouping there are three buckets:

• Player 1 → { 2016-03-01, 2016-05-02 }
• Player 2 → { 2017-06-25 }
• Player 3 → { 2016-03-02, 2018-07-03 }

Step 2 — reduce each bucket with MIN. MIN compares dates as ordered values (lexicographic on ISO YYYY-MM-DD happens to match chronological order):

Result — one row per bucket: (1, 2016-03-01), (2, 2017-06-25), (3, 2016-03-02).

Why the naive intermediate is wrong

A common (and previously-on-this-page) mistake is to show an "after the SELECT, before the GROUP BY" intermediate like:

-- INVALID as a standalone query
SELECT player_id, MIN(event_date)
FROM Activity;        -- no GROUP BY

and then claim it yields five rows (1 | 2016-03-01, 1 | 2016-03-01, 2 | …, 3 | …, 3 | …). That table is fabricated — it cannot occur:

• With ONLY_FULL_GROUP_BY enabled (MySQL 5.7+ default, and the SQL standard), this query is rejected: "player_id is not in GROUP BY clause and contains a nonaggregated column". PostgreSQL, SQL Server, and Oracle reject it outright.
• In MySQL's old loose mode it does not error — but the moment any aggregate (MIN) appears with no GROUP BY, the whole table becomes one single group. So it returns exactly one row: an arbitrary player_id paired with the global minimum date (2016-03-01). Never five rows.

The lesson: there is no "per-row" stage that pairs each player_id with a min. Aggregation operates on groups, and the group is decided by GROUP BY before MIN ever runs. The correct query with GROUP BY player_id is what makes player_id a legal output column.

MIN vs. the window-function alternative

You can also get "earliest per player" with a window function, and it is worth knowing when each wins:

-- Window version: ranks rows within each player
SELECT player_id, event_date AS first_login
FROM (
  SELECT player_id, event_date,
         ROW_NUMBER() OVER (
           PARTITION BY player_id
           ORDER BY event_date
         ) AS rn
  FROM Activity
) t
WHERE rn = 1;

For this problem (only the date is asked for) GROUP BY MIN is the right tool — simpler and cheaper. Reach for the window version when you need the device or games-played of the first login (that is Game Play Analysis II/IV territory).

Pitfalls

• Selecting a bare column that isn't grouped. SELECT player_id, device_id, MIN(event_date) … GROUP BY player_id — device_id is neither grouped nor aggregated. Standard SQL and ONLY_FULL_GROUP_BY reject it; loose MySQL silently returns an arbitrary device_id that may not belong to the first-login row. To return that device, use the window version.
• Assuming MIN breaks ties uniquely. The primary key is (player_id, event_date), so a player cannot have two rows on the same date — first login is unambiguous here. Remove that PK guarantee (e.g., timestamps with duplicates) and MIN still returns one date, but ROW_NUMBER() would arbitrarily pick one of the tied rows unless you add a deterministic tiebreaker in ORDER BY.
• NULL dates. MIN ignores NULLs. If a player had only NULL event_date rows, MIN returns NULL for that group rather than dropping the player — and the player still appears in the result.
• Index angle. A composite index on (player_id, event_date) — which the primary key already provides in most engines — lets the optimizer satisfy MIN(event_date) per group by a loose index scan: it jumps to the first event_date for each player_id prefix instead of scanning every row. Without that ordering the engine must scan and hash/sort all rows.
• String dates. If event_date were stored as text in a non-ISO format (e.g. MM/DD/YYYY), MIN compares lexicographically and returns the wrong "earliest." ISO YYYY-MM-DD or a real DATE type is what makes MIN correct.

Takeaways

• GROUP BY runs logically before SELECT: it forms buckets, then each aggregate reduces one bucket to one value — there is no per-row intermediate to inspect.
• An aggregate with a bare non-grouped column is invalid under ONLY_FULL_GROUP_BY; with no GROUP BY at all it produces exactly one row, never one-per-key.
• Use GROUP BY MIN when you need the value; use a ROW_NUMBER() window when you need the whole earliest row.
• A (player_id, event_date) index turns this into a loose index scan — fast and order-aware — so the right index is also the right correctness guarantee for tie-free first logins.

Sources: LeetCode 511 "Game Play Analysis I"; MySQL 8.0 Reference Manual — "Detection of Functional Dependence" and the ONLY_FULL_GROUP_BY SQL mode; the SQL:2016 standard rules for grouped queries; MySQL's GROUP BY loose index scan optimization. Re-authored and deepened for this guide — the fabricated five-row "Output After Step 1" intermediate was removed and replaced with the correct partition-then-reduce mechanism, a MIN-vs-window comparison, tie/NULL/index pitfalls, and a hand-authored diagram.