Skip to content

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