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

1. 首先查找新用户的首天登录

select 
    max(date(event_time)) as max_date 
    from tb_order_overall

2. 将tb_user_log转换成登陆日志表，使用UNION 对合集时间去重

    select uid,date(in_time) as dt_2
        from tb_user_log 
        union  
        select uid, date(out_time) as dt
        from tb_user_log

3. 将两表以uid合并，再通过dt_2和dt_1的差值，按照dt_1分组后统计每天新用户的次日留存率

 with t1 as (
    select  uid, 
		min(date(in_time)) as dt_1
    from tb_user_log
    group by uid
),

t2 as (
    select uid,date(in_time) as dt_2
        from tb_user_log 
        union  
        select uid, date(out_time) as dt
        from tb_user_log
)

select dt_1, 
    round(COUNT(DISTINCT CASE WHEN datediff(dt_2, dt_1)=1 THEN uid else null end)/count(distinct uid),2) uv_left_rate
    from t2 
    inner join t1 
    using(uid)
    WHERE date_format(dt_1,'%Y-%m') = '2021-11' 
    GROUP BY dt_1
    ORDER BY dt_1