SQL面試必考——計算留存率

leetcode 1097. 遊戲玩法分析 V
計算第一天的留存率

法一：視窗函式

1. 找到安裝遊戲的時間，及每個玩家第一次登陸的時間，通過min()+視窗函式，可以既找到最小值，又保留原來的值。（若使用聚合函式group by則只能保留一組數）

select player_id,
	   event_date,
	   min(event_date) over(partition by player_id) install_dt
from Activity;

2. 計算第一天的登陸玩家人數，和第二天仍然登陸的人數。用group by分組計算。

select distinct install_dt,
	   count(distinct player_id) installs,
	   round(sum(if datediff(event_date,install_date)=1,1,0)/count(distinct player_id),2) Day1_retention
from 
(select player_id,
	   event_date,
	   min(event_date) over(partition by player_id) install_dt
from Activity) t
group by install_dt;

法二：自連線

1. 計算第一天登陸時間
2. 通過左連線能夠得到第一天登陸的id以及第二天是否繼續登陸的資訊，如果第二天沒有登陸，則為null。count(*)會將null算入。

select distinct install_dt,
       count(*) installs,
       round(count(t2.event_date)/count(*),2) Day1_retention
from
(select player_id,min(event_date) install_dt
from Activity
group by player_id) t1
left join Activity t2
on t1.player_id = t2.player_id
and datediff(t2.event_date,t1.install_dt)=1
group by install_dt;

左連線得到的結果如下

t1.player_id	t1.install_dt	t2.player_id	t2.event_date
1	2016-03-01	1	2016-03-02
2	2017-06-25	null	null
3	2016-03-01	null	null