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

            from tb_user_log
            where date(in_time) >= (select date_sub(max(date(in_time)), interval 6 day)
                                    from tb_user_log))
select case when value=1 then '忠实用户'
when value=2 then '新晋用户'
when value=3 then '沉睡用户'
else '流失用户' end as user_grade,
round(count(value)/(select COUNT(distinct uid)
    from tb_user_log),2)
from (select uid, 1 as value
      from  a1
               right join tb_user_log tl using (uid)
      group by uid
      having count(tl.in_time) > 1 #忠实用户
      union all
      select a1.uid, 2 as value
      from a1
               right join tb_user_log tl using (uid)
      group by uid
      having count(tl.in_time) = 1 #新晋用户
      union all
      select uid, 3 as value
      from tb_user_log
      where uid not in (select uid
                        from tb_user_log
                        where date(in_time) >= (select date_sub(max(date(in_time)), interval 6 day)
                                                from tb_user_log))
        and date(in_time) >= (select date_sub(max(date(in_time)), interval 30 day)
                              from tb_user_log) #沉睡用户
      union all
      select uid, 4 as value
      from tb_user_log
      where uid not in (select uid
                        from tb_user_log
                        where date(in_time) >= (select date_sub(max(date(in_time)), interval 6 day)
                                                from tb_user_log))
        and date(in_time) < (select date_sub(max(date(in_time)), interval 30 day)
                             from tb_user_log) #流失用户
     )b
group by user_grade