【Interview question】A game data background has two tables: "login log" and "logout log".
The Login Log records the login time of each player and the level of the character at the time of login.
The "Logout Log" records each player's time of sign-out and the level of their character at the time of sign-out.
Where, the "Character id" field uniquely identifies the player.
The following are the character login and logout logs two days before the game is launched (2022-08-13 to 2022-08-14).
During the day, players can log in to the game multiple times, and use SQL to analyze the following business problems:
Analyze the first day of service (2022-08-13), the GAME's DAU (number of daily active players) and next-day retention rate (the number of active players still logged in the next day / the total number of active players on the day)
【Problem Solving Ideas】
Question 1: Analyze the first day of service (2022-08-13), the GAME's DAU (number of daily active players) and next-day retention rate (the number of active players still logged in the next day / the total number of active players on the day)
1. Calculate the DAU (number of daily active players) of the game on the first day of service
The first step in the game's DAU (number of daily active players) is to clarify what is the number of daily active players, and the number of daily active players refers to the number of non-repeating players who have logged into the game at least once on the day.
According to the meaning of the title, the day is the first day of service (2022-08-13), so we want to use the where clause to filter out the data dated "2022-08-13":
Logging in to the game at least once means logging in to the game, the "login log" records the player's login information, and the player has logged in to the game.
Player login information may be missing in the "login log", so in order to avoid missing the player, we take the data from the "login log" to query:
How is the number of players calculated?
Counting the number of players is counting the number of players, and the count() function has a counting function, so we use the count() function to calculate the number of players; the player uses the "character id" unique identification, a "character id" corresponds to a player, so we count the "character id":
Since players can log in to the game multiple times in a day, there will be duplicate "character iDs" in the login log, in order to calculate the number of non-repeating players, we also need to use the static clause to re-emphasize the "character id", that is, to calculate the number of non-repeating players as:
Combine the above analysis into a complete SQL statement according to the SQL writing specification to calculate the number of daily active players.
Complete SQL writing method:
The query results are as follows:
2. Next-day retention rate
Because the next day retention rate = the number of active players still logged in the next day / the total number of active players on the same day.
Therefore, the retention rate of the next day on the first day of service = the number of active players who are still logged in on the day after the start of service (2022-08-14) / the total number of active players on the first day of service.
The total number of active players on the first day of service is the DAU of the first day of service, we have calculated it earlier, and now we will calculate the number of active players who are still logged in on the day after the launch.
The number of active players who are still logged in on the day after the launch of the service indicates: the number of non-repeating players who have logged in on the first day of the service and are still logged in on the day after the start of the service. There are multiple filters here:
1) Filter the players who logged in the next day (2022-08-14) from the "Login Log":
2) And the player belongs to the player who logged in on the first day of the service (2022-08-13):
2022-08-13Members who have logged in can filter out with the following SQL statement:
Therefore, the statement to filter players who are still logged in the day after the launch from the Login Log is:
Once the players are screened, the number of non-repeating players can be counted, and the count (distinct character id) can be used to calculate the number of non-repeating players.
Therefore, the complete SQL statement to calculate the number of active players who are still logged in on the day after the launch of the service is written as follows:
Now, we calculate the retention rate of the next day on the first day of service based on the previous results.
The SQL method for calculating the total number of active players on the first day of service (2022-08-13) is as follows:
According to the calculation method of the next-day retention rate, the count (distinct character id) that calculates the number of active players still logged in on the next day / the count (distinct role id) that calculates the number of active users logged in on the first day is the next-day retention rate.
Therefore, the SQL statement that calculates the total number of active players on the first day of the service can be brought into the SQL statement that calculates the number of active players who are still logged in on the day after the service is launched, and the division operation is performed.
How to write a complete SQL:
That is, the next-day retention rate on the first day of service (2022-08-13) is 75%.
Case data download methods: