LeetCode 511 - Game Play Analysis I¶

Database Language: MySQL

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 Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'leetcode.Activity.player_id'; this is incompatible with sql_mode=only_full_group_by

The query plan generated by MySQL is as follows:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	Activity		index	PRIMARY	PRIMARY	7		5	100.00	Using index

As can be seen from this query plan, only the index was used to execute the query because all the columns referenced by the query are in the PRIMARY KEY index, which makes running the query very efficient.

Runtime: 426ms
Beats: 95.75% as of July 15, 2024