Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/1097.html
1097. Game Play Analysis V
Level
Hard
Description
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 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.
We define the install date of a player to be the first login day of that player.
We also define day 1 retention of some date X to be the number of players whose install date is X and they logged back in on the day right after X, divided by the number of players whose install date is X, rounded to 2 decimal places.
Write an SQL query that reports for each install date, the number of players that installed the game on that day and the day 1 retention.
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-03-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-01 | 0 |
| 3 | 4 | 2016-07-03 | 5 |
+-----------+-----------+------------+--------------+
Result table:
+------------+----------+----------------+
| install_dt | installs | Day1_retention |
+------------+----------+----------------+
| 2016-03-01 | 2 | 0.50 |
| 2017-06-25 | 1 | 0.00 |
+------------+----------+----------------+
Player 1 and 3 installed the game on 2016-03-01 but only player 1 logged back in on 2016-03-02 so the day 1 retention of 2016-03-01 is 1 / 2 = 0.50
Player 2 installed the game on 2017-06-25 but didn't log back in on 2017-06-26 so the day 1 retention of 2017-06-25 is 0 / 1 = 0.00
Solution
For install_dt, select the minimum date of each player. A minimum date of a player is a date such that no date is less than the player’s date. For installs, count the number of players that logged in on the day. For Day1_retention, count the number of players that logged back in on the day right after the install date. Use round to round the results to 2 decimal places.
# Write your MySQL query statement below
select a1.event_date as install_dt, count(a1.player_id) as installs, round(count(a3.player_id) / count(a1.player_id), 2) as Day1_retention
from Activity a1 left join Activity a2
on a1.player_id = a2.player_id and a1.event_date > a2.event_date
left join Activity a3
on a1.player_id = a3.player_id and datediff(a3.event_date, a1.event_date) = 1
where a2.event_date is null
group by a1.event_date;