First, give the Activity table used by the four questions:

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

511. Game play analysis I

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

Submit answer:

# Write your MySQL query statement below
select player_id, min(event_date) 'first_login'
from Activity
group by player_id

Analysis: grouping + min function.

512. Game play analysis II

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

Submit answer:

# Write your MySQL query statement below
# Method 1
select a.player_id, a.device_id
from Activity a JOIN
(select player_id, min(event_date) 'first_login'
from Activity group by player_id) t
ON a.player_id=t.player_id and a.event_date = t.first_login;
#Method 2
select player_id, device_id
from activity a1
where a1.event_date<=all(select a2.event_date from activity a2 where a1.player_id=a2.player_id);

Resolution:

• Scheme 1: first find the first login date, and then use this temporary table t and activity table a to make an internal connection;

• Scenario 2: use the all connector to connect the current event_date value and player returned by subquery_ Even with the same ID_ Date comparison (all values less than or equal to find the minimum value)

  WHERE column_name comparison_operator ALL (subquery)
  

534. Game play Analysis III

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.

Submit answer:

# Write your MySQL query statement below
select player_id, event_date, sum(games_played) over(partition by player_id order by event_date) 'games_played_so_far'
from Activity

Resolution:

• Partition by player in the over function_ ID indicates sum accumulation function according to player_id for grouping;
• When the order by in the over function is accumulated, the date is arranged in ascending order, which is accumulated from the first row to the last row of the current group;
• Assuming sum(games_played) over(partition by player_id) is used, the sum function calculates the total number of games played by the current user;
• Assuming that only order by is used instead of partition by, sum() over() accumulates from the first row to the current row by default!

550. Game play analysis IV

Write an SQL query to report the rate of players who log in again the day after the first login, rounded to two decimal places. In other words, you need to calculate the number of players who have logged in for at least two consecutive days from the first login date, and then divide it by the total number of players.

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-03-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+

Result table:
+-----------+
| fraction  |
+-----------+
| 0.33      |
+-----------+
only ID The player with 1 only logs in again after logging in on the first day, so the answer is 1/3 = 0.33

Submit answer:

# Write your MySQL query statement below
select ROUND(sum(CASE WHEN a.event_date is NULL THEN 0 ELSE 1 END)/count(*), 2) 'fraction'
from
(select player_id, min(event_date) 'first_login'
from Activity
group by player_id) t
LEFT JOIN
Activity a
ON DATE_ADD(t.first_login, INTERVAL 1 DAY)=a.event_date and a.player_id=t.player_id

Resolution:

• First, the meaning of our temporary table t is: the id corresponding to each player and the first login date;

  (select player_id, min(event_date) 'first_login'
  from Activity
  group by player_id) t
  

• Temporary table t LEFT JOIN left join activity a. The connection condition is that A. date = T. first login date + 1. The id of player is the same. Reasons for using LEFT JOIN: the number of all users needs to be counted in select;

• count(*) counts the number of player s. In sum(CASE WHEN a.event_date is NULL THEN 0 ELSE 1 END), the event in the right table_ Date is calculated only when it is not empty;

• ROUND(cal, 2) keeps two decimal places.

The above is the solution of four sql in game play analysis I to IV. if there is any error or solution problem, please correct it. 🙂