Hive Solution

3.Hive Solution

-- Thinking1:每個player_id最小的act日期為註冊日
-- Thinking2:每個player_id註冊日的下一個event_date為註冊日期+1，即是day1_retention

-- ## solution1
SELECT 
a.install_dt,
count(a.player_id) as installs,
count(if( date_add(a.install_dt,1) = b.event_date_next,a.player_id,null)) 
	as reten_player,
count(if( date_add(a.install_dt,1) = b.event_date_next,a.player_id,null))
	/count(a.player_id) as day1_retention
FROM  
(select player_id, min(event_date) as install_dt
    from leetcode.ex_1097_activity group by player_id) a 
left join  
(select player_id, event_date,
	lead(event_date)over(partition by player_id order by event_date) 
	    as event_date_next
		from leetcode.ex_1097_activity) b 
	    ON a.player_id=b.player_id 
		and a.install_dt=b.event_date
GROUP BY a.install_dt ; 

	
	
-- ## solution2
SELECT 
a.install_dt,
count(a.player_id) as installs ,
count(b.event_date)/count(a.player_id) as day1_retention
FROM 
(select player_id, min(event_date) as install_dt
    from leetcode.ex_1097_activity group by player_id) a 
 left join 
 (select player_id,event_date from leetcode.ex_1097_activity) b 
 			ON a.player_id=b.player_id 
       				and date_add(install_dt,1)=event_date
 GROUP BY a.install_dt ;