【每日SQL打卡】​​​​​​​​​​​​​​​DAY 12丨游戏玩法分析 V【难度困难】

[海哥刁德一]

该题主要使用了row_number() 排序以及偏移函数lead，大致逻辑如下

with temp  as (
SELECT
*,row_number() over(partition by player_id order by event_date) as rn
,lead(event_date) over(partition by player_id order by event_date) as nx_event_date
FROM activity)

SELECT 
 event_date as install_dt
,count(player_id)  as installs
,sum(case when nx_event_date - event_date = 1 then 1 else 0 end)/count(player_id) as Day1_retention 

from temp 
where rn =1
group by event_date