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