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

留存率的计算可以说是数据分析师, 或者数据开发必备的技能了, 很多公司都会用到这个指标, 该题还有进阶版的: 计算次日至7日内的新增用户留存率

1. 思路: 根据uid分组, 取min(in_time)最小值, 得到用户的首次登录日期, 作为衍生表t1

    select ul.uid,
        min(date(ul.in_time)) min_date
    from tb_user_log ul
    group by ul.uid

2. 因为题目要求, out_time跨天也算该日活跃, 那么第二个子查询, 只需要关键字, uid, in_time, out_time即可, 这里其实可以直接用衍生表t1的uid左连接tb_user_log, 这样能得到如下的格式数据(假设, id为1的用户, 在1-3号都有登录过, 则会出现3条数据

   t1.uid	t1.min_date	ul.uid	ul.in_time	ul.out_time
   1	2022-11-01	1	2022-11-01	2022-11-01
   1	2022-11-01	1	2022-11-02	2022-11-02
   1	2022-11-01	1	2022-11-03	2022-11-03

3. 如上表, 可以再根据, t1.min_date 进行分组, 对 t1.uid 进行去重, 则可得到该日的新增用户数, 而下一
   天的用户数, 则可使用case when 对ul.in_time 和 out_time 进行判断, 这里我用的是

   count(distinct case when t2.l_in_time = date_add(t1.min_date, interval 1 day)
       or t2.l_out_time = date_add(t1.min_date, interval 1 day) then t2.uid
       else null end)

   同理, 3-7日的留存率也可如此计算.

   优化点: 一个用户在一天可以登录多次

   这样直接左关连tb_user_log(此表生产环境中一般都极大), 所以可以根据uid, date(ul.in_time), date(ul.out_time) 来缩小连表的数据量, 一个用户一天只需要保留一条数据即可

select t1.min_date as dt,
    round(count(distinct case when t2.l_in_time = date_add(t1.min_date, interval 1 day)
          or t2.l_out_time = date_add(t1.min_date, interval 1 day) then t2.uid
          else null end)/count(distinct t1.uid), 2) as uv_left_rate
from (
    select ul.uid,
        min(date(ul.in_time)) min_date
    from tb_user_log ul
    group by ul.uid
) t1 left join (
    select ul.uid,
        date(ul.in_time) l_in_time,
        date(ul.out_time) l_out_time
    from tb_user_log ul
) t2 on t1.uid = t2.uid
where min_date between '2021-11-01' and '2021-12-01'
group by min_date
order by min_date