题解 | #日活次日留存率和新户次日留存率#

#小白简化思路，直接case when + group by判断
#求每天的日活用户次日留存率，等于当日+1=向上一个日期相同，则符合条件。
#求每天新用户次日留存率，等于用户最小登录日期=当期日期 and 当日+1=向上一个日期相同,则符合新用户次日留存日期。
#group by 天数，用avg(日活用户次日留存率)，备注avg( 当日+1=向上一个日期相同 then 1 else 0) 或者 sum(当日+1=向上一个日期相同 then 1 else 0)/count(时间)
#特别注意，判断新用户日活是2个条件，公式为新用户日活人数/新用户人数，sum(最小登录日期=当期日期 and 当日+1=向上一个日期相同 then 1 else 0 end)/sum(最小登录日期=当期日期 then 1 end).实际可以avg(条件)但是没有的也会输出为0，所以，此题不推荐。
with tiaojian as (
select 
uid,
login_date,
lead(login_date,1)over(partition by uid order by login_date) as shr,
min(login_date)over(partition by uid order by login_date) as sr,
date_add(login_date,interval 1 day) as cr
from user_login_tb
)


select 
login_date,
round(
avg( case when shr=cr then 1 else 0 end),2) as uv_left_rate,
round(
sum( case when sr=login_date and  shr=cr then 1 else 0 end)/
sum(case when sr=login_date then 1 else 0 end),2)  as new_uv_left_rate
from tiaojian
group by login_date
order by login_date