LeetCode 511 - Game Play Analysis I¶

Database Language: SQL Server

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 [dbo].[Activity] (player_id INT NOT NULL, device_id INT, event_date DATE NOT NULL, games_played INT);
ALTER TABLE [dbo].[Activity] ADD CONSTRAINT PK_Ativity PRIMARY KEY (player_id, event_date);

TRUNCATE TABLE [dbo].[Activity];
INSERT INTO [dbo].[Activity] (player_id, device_id, event_date, games_played) VALUES ('1', '2', '2016-03-01', '5');
INSERT INTO [dbo].[Activity] (player_id, device_id, event_date, games_played) VALUES ('1', '2', '2016-05-02', '6');
INSERT INTO [dbo].[Activity] (player_id, device_id, event_date, games_played) VALUES ('2', '3', '2017-06-25', '1');
INSERT INTO [dbo].[Activity] (player_id, device_id, event_date, games_played) VALUES ('3', '1', '2016-03-02', '0');
INSERT INTO [dbo].[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:

Query 1 ERROR: Msg: 8120, Line 1, State: 1, Level: 16
Column 'Activity.player_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The query plan generated by SQL Server is as follows:

  |--Stream Aggregate(GROUP BY:([leetcode].[dbo].[Activity].[player_id]) DEFINE:([Expr1002]=MIN([leetcode].[dbo].[Activity].[event_date])))
    |--Clustered Index Scan(OBJECT:([leetcode].[dbo].[Activity].[PK_Ativity]), ORDERED FORWARD)

Here's the fastest runtime for this query:

Runtime: 1224ms
Beats: 93.19% as of July 16, 2024