Leetcode刷题记之游戏玩法分析V

报告每个安装日期、当天安装游戏的玩家数量和第一天的保留时间

题目来源：Leetcode 1097

预期输出结果：

>>>读题： 安装日期，即每个玩家第一次登陆日期；当天安装游戏的玩家；第一天的保留时间：次日留存率

① 安装日期：最小日期即为首次登陆日期

SELECT MIN(event_date) install_dt,player_id
FROM activity
GROUP BY player_id

输出：

当天安装游戏的玩家数：count(player_id)

② 关联求解：次日留存 -- 首次登陆日第二天登陆玩家数/当天安装游戏的玩家数 

SELECT a.install_dt, COUNT(a.player_id) installs, 
       round(count(b.player_id)/count(a.player_id),2) Day1_retention
FROM (
	SELECT MIN(event_date) install_dt,player_id
	FROM activity
	GROUP BY player_id) a
LEFT JOIN activity b ON Datediff(b.event_date,a.install_dt) = 1
		AND a.player_id = b.player_id
GROUP BY a.install_dt

输出：

想象困难患者友好：中间过程辅助

SELECT *
FROM
     ((SELECT MIN(event_date) install_dt,player_id install_player_id
	  FROM activity
	GROUP BY install_player_id) a
	LEFT JOIN activity b ON Datediff(b.event_date,a.install_dt) = 1
			     AND a.install_player_id = b.player_id )

输出：

DONE !