LeetCode 511: Game Play Analysis I

Problem Restatement

We are given an Activity table.

Each row records one player login on one date, together with the device used and the number of games played.

We need to report the first login date for each player.

The result can be returned in any order. The table has columns player_id, device_id, event_date, and games_played, and the goal is to find the earliest event_date for every player_id.

Table

The primary key is:

(player_id, event_date)

This means one player has at most one row for a given date.

Output

Example

Input:

For player 1, the login dates are:

2016-03-01
2016-05-02

The first login is:

2016-03-01

For player 2, there is only one row, so the first login is:

2017-06-25

For player 3, the first login is:

2016-03-02

Output:

First Thought: One Row Per Player

The result needs one row per player.

Whenever a SQL problem asks for one row per group, we should think about:

GROUP BY

Here, the group is:

player_id

Inside each player group, we need the earliest date.

The aggregate function for the earliest value is:

MIN(event_date)

Key Insight

The question does not ask for the whole first login row.

It only asks for:

player_id
first_login date

So we do not need joins, subqueries, or window functions.

We can group all rows by player and take the minimum login date in each group.

Algorithm

Use SQL aggregation:

1. Read rows from Activity.
2. Group rows by player_id.
3. For each group, compute MIN(event_date).
4. Alias that value as first_login.

Correctness

GROUP BY player_id partitions the table into one group per player.

Within each group, MIN(event_date) returns the earliest login date for that player.

The query outputs exactly one row for each player group, with the player id and that earliest date.

Therefore the result contains the first login date for every player.

Complexity

Here:

With a suitable index on (player_id, event_date), the database can compute this efficiently.

Implementation

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

Code Explanation

Select the player id:

SELECT
    player_id,

Compute the earliest login date for that player:

MIN(event_date) AS first_login

Read from the table:

FROM Activity

Group rows by player:

GROUP BY player_id;

After grouping, each output row corresponds to exactly one player.

Testing

Test table:

CREATE TABLE Activity (
    player_id INT,
    device_id INT,
    event_date DATE,
    games_played INT,
    PRIMARY KEY (player_id, event_date)
);

INSERT INTO Activity (player_id, device_id, event_date, games_played) VALUES
    (1, 2, '2016-03-01', 5),
    (1, 2, '2016-05-02', 6),
    (2, 3, '2017-06-25', 1),
    (3, 1, '2016-03-02', 0),
    (3, 4, '2018-07-03', 5);

Expected result:

Additional test case:

INSERT INTO Activity (player_id, device_id, event_date, games_played) VALUES
    (4, 9, '2020-01-10', 0),
    (4, 8, '2019-12-31', 3);

For player 4, the first login should be:

2019-12-31

Test meaning: