511. Game play analysis I

Activity table:

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+

The primary key of the table is (player_id, event_date).
This table shows the behavior and activities of some game players on the game platform.
Each line of data records the number of Games opened by a player after logging in to the platform with the same device on the same day before exiting the platform (possibly 0).

Write an SQL query statement to get the date when each player first landed on the platform.

The format of query results is as follows:

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  |
+-----------+-------------+

SQL

select player_id, min(event_date) as first_login
from Activity
group by player_id
;

512. Game play analysis II

Table: Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+

(player_id, event_date) are the two primary keys of this table
This table shows the game activities of some game players
Each line is a record of players who logged in and played multiple games (possibly 0) before an angel logged out with a device
Please write an SQL query to describe the device name of each player's first login

The query result format is in the following example:

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 | device_id |
+-----------+-----------+
| 1         | 2         |
| 2         | 3         |
| 3         | 1         |
+-----------+-----------+

thinking

1. Find out the time of each user's first login (that is, the previous question)
2. Then filter out the answers according to the time of the first login

SQL

select player_id, device_id
from Activity
where (player_id, event_date) in (
        select player_id, min(event_date)
        from Activity
        group by player_id
    )
;

534. Game play Analysis III

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 of this table.
This table shows the activities of players in some games.
Each line is a record of a player who logged in and played a lot of games (maybe 0) before logging out with a device one day.

Write an SQL query and report each group of players and dates, as well as how many games players have played so far. That is, the total number of games played by players before this date. Please see the example for details.

The query result format is as follows:

Activity table:

+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-05-02 | 6            |
| 1         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+

Result table:

+-----------+------------+---------------------+
| player_id | event_date | games_played_so_far |
+-----------+------------+---------------------+
| 1         | 2016-03-01 | 5                   |
| 1         | 2016-05-02 | 11                  |
| 1         | 2017-06-25 | 12                  |
| 3         | 2016-03-02 | 0                   |
| 3         | 2018-07-03 | 5                   |
+-----------+------------+---------------------+
about ID Players of 1, 2016-05-02 We played for five years+6=11 Games, 2017-06-25 We played for five years+6+1=12 A game.
about ID Players for 3, 2018-07-03 Played 0 in total+5=5 A game.
Please note that for each player, we only care about the player's login date.

thinking

1. According to player_id inner connection
2. Keep only T1 event_ date >= t2. event_ date
3. According to T1 player_ ID and T1 event_ Date grouping
4. T2 after grouping games_ Played summation

The detailed process is as follows

t1.player_id	t1.event_date	t1.games_played
1	2016-03-01	5
1	2016-05-02	6
1	2017-06-25	1
3	2016-03-02	0
3	2018-07-03	5

t2.player_id	t2.event_date	t2.games_played
1	2016-03-01	5
1	2016-05-02	6
1	2017-06-25	1
3	2016-03-02	0
3	2018-07-03	5

Step 1: according to the player_id inner connection

t1.player_id	t1.event_date	t1.games_played	t2.player_id	t2.event_date	t2.games_played
1	2016-03-01	5	1	2016-03-01	5
1	2016-03-01	5	1	2016-05-02	6
1	2016-03-01	5	1	2017-06-25	1
1	2016-05-02	6	1	2016-03-01	5
1	2016-05-02	6	1	2016-05-02	6
1	2016-05-02	6	1	2017-06-25	1
1	2017-06-25	1	1	2016-03-01	5
1	2017-06-25	1	1	2016-05-02	6
1	2017-06-25	1	1	2017-06-25	1
3	2016-03-02	0	3	2016-03-02	0
3	2016-03-02	0	3	2018-07-03	5
3	2018-07-03	5	3	2016-03-02	0
3	2018-07-03	5	3	2018-07-03	5

Step 2: keep only T1 event_ date >= t2. event_ date

t1.player_id	t1.event_date	t1.games_played	t2.player_id	t2.event_date	t2.games_played
1	2016-03-01	5	1	2016-03-01	5
1	2016-05-02	6	1	2016-03-01	5
1	2016-05-02	6	1	2016-05-02	6
1	2017-06-25	1	1	2016-03-01	5
1	2017-06-25	1	1	2016-05-02	6
1	2017-06-25	1	1	2017-06-25	1
3	2016-03-02	0	3	2016-03-02	0
3	2018-07-03	5	3	2016-03-02	0
3	2018-07-03	5	3	2018-07-03	5

Step 3: according to T1 player_ ID and T1 event_ Date grouping

t1.player_id	t1.event_date	t1.games_played	t2.player_id	t2.event_date	t2.games_played
1	2016-03-01	5	1	2016-03-01	5
1	2016-05-02	6	1	2016-03-01	5
1	2016-05-02	6	1	2016-05-02	6
1	2017-06-25	1	1	2016-03-01	5
1	2017-06-25	1	1	2016-05-02	6
1	2017-06-25	1	1	2017-06-25	1
3	2016-03-02	0	3	2016-03-02	0
3	2018-07-03	5	3	2016-03-02	0
3	2018-07-03	5	3	2018-07-03	5

Step 4: T2 after grouping games_ Played summation

player_id	event_date	games_played_so_far
1	2016-03-01	5
1	2016-05-02	11
1	2017-06-25	12
3	2016-03-02	0
3	2018-07-03	5

SQL

select t1.player_id, t1.event_date, sum(t2.games_played) as games_played_so_far
from Activity as t1 inner join Activity as t2
on t1.player_id = t2.player_id 
where t1.event_date >=  t2.event_date
group by t1.player_id, t1.event_date
;