1097. Game Play Analysis V 难度：困难

1、题目描述

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

来源：力扣（LeetCode）

2、解题思路

这一题，难度在于两个比较值‘安装日期’和‘第1天留存’，‘安装日期’可以直接按日期统计，但是‘第1天留存’必须先按玩家统计，然后转换成日期统计。
1# 首先‘安装’日期比较简单，直接进行count(player_id) as installs，但是注意要先进行排序order by player_id,event_date

select event_date as install_dt,count(player_id) as installs  
from

(select  player_id,event_date
from Activity
group by player_id
order by player_id,event_date)a 

group by event_date

2# ‘第1天留存’，两表联查，取日期差为1，datediff(a2.event_date,a1.event_date)=1

select a1.event_date,count(a1.player_id) as jude
from Activity a1,Activity a2
where a1.player_id=a2.player_id and datediff(a2.event_date,a1.event_date)=1
group by a1.event_date

3# 2表联查，注意空置处理ifnull(jude,0)

3、提交记录

select install_dt,installs,round(ifnull(jude,0)/installs,2) as Day1_retention
from (
    
select event_date as install_dt,count(player_id) as installs  
from
(select  player_id,event_date
from Activity
group by player_id
order by player_id,event_date)a 
group by event_date)c

left join

 (select a1.event_date,count(a1.player_id) as jude
from Activity a1,Activity a2
where a1.player_id=a2.player_id and datediff(a2.event_date,a1.event_date)=1
group by a1.event_date)b

on c.install_dt=b.event_date
group by install_dt
order by install_dt