Game Play Analysis I Problem


Description

LeetCode Problem 511.

Table: Activity

1
2
3
4
5
6
7
8
9
10
11
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
(player_id, event_date) is the primary key of this table.
This table shows the activity of players of some game.
Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on some day using some device.

Write an SQL query that reports the first login date for each player.

The query result format is in the following example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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            |
+-----------+-----------+------------+--------------+

Result table:
+-----------+-------------+
| player_id | first_login |
+-----------+-------------+
| 1         | 2016-03-01  |
| 2         | 2017-06-25  |
| 3         | 2016-03-02  |
+-----------+-------------+


MySQL Solution

1
2
3
select a.player_id, min(a.event_date) as first_login
from Activity a
group by a.player_id




Related Posts

Median Employee Salary Problem

LeetCode 569. Write a SQL query to find the median...

Trips And Users Problem

LeetCode 262. Write a SQL query to find the cancellation...

Game Play Analysis IV Problem

LeetCode 550. Write an SQL query that reports the fraction...

Game Play Analysis III Problem

LeetCode 534. Write an SQL query that reports for each...

Game Play Analysis II Problem

LeetCode 512. Write a SQL query that reports the device...

Game Play Analysis I Problem

LeetCode 511. Write an SQL query that reports the first...