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

select
	lef.dt_in as dt
	, round(avg(lef.flag), 2) as uv_left_rate
from(
    select
        dt1.uid # 所有首次活跃用户
		, dt1.dt_in # 首次活跃日期
        , (case when datediff(dt1.dt_out, dt1.dt_in) = 1 then 1
                when datediff(dt2.dt_in, dt1.dt_in) = 1 then 1 else 0 end) as flag
    from(
        select
            *
        from(
            select  # 查找每个用户首次活跃日期和首次离开日期
                ug.uid
                , date(ug.in_time) as dt_in
                , date(ug.out_time) as dt_out
                , row_number() over (partition by ug.uid order by date(ug.in_time)) as rn_in
            from tb_user_log as ug
            ) as uv
        where uv.rn_in = 1  # 首次进入和首次离开
        ) as dt1
    left join
        (
        select
            *
        from(
            select  # 查找每个用户的第二次活跃日期
                ug.uid
                , date(ug.in_time) as dt_in
                , date(ug.out_time) as dt_out
                , row_number() over (partition by ug.uid order by date(ug.in_time)) as rn_in
            from tb_user_log as ug
            ) as uv
        where uv.rn_in = 2
        ) as dt2 on dt1.uid = dt2.uid # 每个用户的首次活跃，首次离开，第二次活跃
    ) as lef
where date_format(lef.dt_in, "%Y-%m") = "2021-11"
group by lef.dt_in 
order by dt