题解 | #2021年11月每天新用户的次日留存率#

select 新增日期
#,sum(if(顺序=1,1,0))新增
#,sum(if(DATE_add(新增日期,INTERVAL 1 day)=time and 顺序<>1 ,1,0)) 次日留存
,round(sum(if(DATE_add(新增日期,INTERVAL 1 day)=time and 顺序<>1 ,1,0))/sum(if(顺序=1,1,0)),2) 次日留存率
from 
(
    select *,case when 顺序=1 then time  else DATE_SUB(time,INTERVAL (顺序-1) day) end '新增日期'
    from 
        (
        select a.*
        , row_number()  over(PARTITION by uid order by time asc) 顺序 
        from(
                select uid,DATE_FORMAT(in_time,'%Y-%m-%d') time from tb_user_log
                union 
                select uid,DATE_FORMAT(out_time,'%Y-%m-%d') time from tb_user_log
                )a
            )b
         
)c
where DATE_FORMAT(新增日期,'%Y-%m')='2021-11'
GROUP BY 新增日期
HAVING 次日留存率 is not null
order by 新增日期 asc