次日留存率=第一天新增用户中第二天依然登录的用户数/第一天新增用户总数
写完之后觉得自己又写啰嗦了(手动捂脸),“少啰嗦,先看东西”
1.利用窗口函数,求出每个用户的初始登陆日期;
2.在上表基础上,利用 case when 分组行转列,如果 date 减去初始登陆日期为1,则这条记录为次日登陆记录,记为1,反之不是,外层求和,输出结果;
3.以上两步结果为上,然后次日登陆列求和,user_id 计数,相除,保留3位小数,即为次日留存率
select round(sum(b.次日登陆)/count(b.user_id),3) as p from (select a.user_id ,sum(case when a.date=date_add(a.first_date,interval 1 day) then 1 else 0 end) as "次日登陆" ,sum(case when a.date <> date_add(a.first_date,interval 1 day) then 1 else 0 end) as "未次日登陆" from (select * ,min(date) over(partition by user_id) as first_date from login) a group by a.user_id) b