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

with a1 as(
    select uid, date(min(in_time)) as dt1
    from tb_user_log
    group by uid
),
a2 as (
    select uid, date(in_time) as dt
    from tb_user_log
),
a3 as (
    select uid, date(out_time) as dt
    from tb_user_log
),
a4 as (
    select uid, dt
    from a2
    union
    select uid, dt
    from a3
),
a5 as (
    select uid, date_sub(dt, interval 1 day) as dt, uid*2 as num
    from a4
),
a6 as (
    select a1.dt1 as dt, round(sum(case when num is not null then 1 else 0 end)/count(*),2) as uv_left_rate
    from a5 right join a1 on a1.uid = a5.uid and a1.dt1 = a5.dt
    group by a1.dt1
)

select dt, uv_left_rate
from a6
where dt>= '2021-11-01' and dt<='2021-11-30'
order by dt