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

• 注意点：
  • 判断用户是否为新用户（首次登陆日期，注意口径）
  • 判断是否为次日留存（是否与首次登陆日期相差1天）
  • 特殊情况：新用户退出时间跨天属于次日留存
• 主要步骤：
  1. 按用户id、进入时间、退出时间进行去重；
  2. 查询出每个用户的首次登陆时间与下次登陆时间，这里注意首次是要统计11月以前，即有些用户在11月1日的时候是老用户，因此这里不能直接去掉11月以前的数据；
  3. 筛选出需要的数据（限定年月、当天为新用户），判断新用户是否为次日留存。当首次登陆日期与次日登陆日期相差1天时，或首次登陆日期与退出观看日期相差1天时均记为次日留存；
  4. 分别统计新用户次日留存数与新用户数，按日期分组汇总出次日留存率，保留小数与排序。

select date_format(in_time, '%Y-%m-%d')               as dt,
       round(sum(case
                     when datediff(second_date, first_date) = 1 then 1
                     when datediff(out_time, first_date) = 1 then 1
                     else 0 end) / count(in_time), 2) as uv_left_rate
from (
         select *,
                min(in_time) over (partition by uid order by in_time)  as first_date,
                lead(in_time) over (partition by uid order by in_time) as second_date
         from (select distinct uid,
                               in_time,
                               out_time
               from tb_user_log
              ) as t
     ) as u
where year(in_time) = 2021
  and month(in_time) = 11
  and first_date = in_time
group by date_format(in_time, '%Y-%m-%d')
order by dt;