LeetCode 511 - Game Play Analysis I¶

Database Language: PostgreSQL

Difficulty:

Problem Description¶

Input¶

Table: Activity¶

Column Name	Type
player_id	int
device_id	int
event_date	date
games_played	int

(player_id, event_date) is the primary key (combination of columns with unique values) of this table. This table shows the activity of players of some games. Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on someday using some device.

Requirement¶

Write a solution to find the first login date for each player.

Return the result table in any order.

The result format is in the following example.

Examples¶

Example 1¶

Input¶

Activity table:

player_id	device_id	event_date	games_played
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

Output¶

player_id	first_login
1	2016-03-01
2	2017-06-25
3	2016-03-02

SQL Schema¶

CREATE TABLE IF NOT EXISTS Activity (player_id INT NOT NULL, device_id INT, event_date DATE NOT NULL, games_played INT);
ALTER TABLE Activity ADD CONSTRAINT PK_Ativity PRIMARY KEY (player_id, event_date);

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

Solution¶

Given that the requirement is simply asking for the first login date for each player, this calls for the MIN aggregate function. The MIN aggregate function returns the minimum (lowest) value in a set. The query to get the first login date for each player is as follows:

# Final Solution Query
SELECT player_id, MIN(event_date) AS first_login
FROM Activity
GROUP BY player_id

Since the requirement wants the first login date to be called first_login, an alias is assigned to the MIN(event_date). Also, the GROUP BY player_id is included in the query because not all columns in the SELECT statement are using an aggregate function (in this case, the player_id column). If the GROUP BY player_id is omitted, the following error will be generated:

ERROR:  column "activity.player_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT player_id, MIN(event_date) AS first_login
               ^ 

SQL state: 42803
Character: 8

The query plan generated by PostgreSQL is as follows:

HashAggregate  (cost=37.75..39.75 rows=200 width=8)
  Group Key: player_id
  ->  Seq Scan on activity  (cost=0.00..28.50 rows=1850 width=8)

Here's the fastest runtime for this query:

Runtime: 498ms
Beats: 91.89% as of July 15, 2025