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

select
    substr(aggr_dt,1,10) as dt
    ,round(count(distinct case when (rn = 2 and substr(date_add(aggr_dt,interval 1 day),1,10) = substr(in_time,1,10)) or (rn = 1 and substr(date_add(in_time,interval 1 day),1,10) = substr(out_time,1,10)) then id end)/count(distinct case when rn =1 then id end),2) as uv_left_rate
    -- ,count(distinct case when rn = 1 then id end) as first_d
    -- ,count(distinct case when rn = 2 then id end) as second_d
    -- *
    -- ,substr(date_add(aggr_dt,interval 1 day),1,10) as dt1
    -- ,substr(in_time,1,10) as dt2
from (
    select 
        *
        ,rank() over(partition by uid order by in_time) rn 
        ,coalesce(lag(in_time) over(partition by uid order by in_time),in_time) aggr_dt
    from tb_user_log
) t
where substr(aggr_dt,1,7) = '2021-11'
group by substr(aggr_dt,1,10) 
;