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

创建2个表 一个表是所有用户的登录情况表active_user_t t1（date，uid）

    select uid,in_time
    from tb_user_log
    where date_format(in_time,'%Y-%m')='2021-11'
    union
    select uid,out_time
    from tb_user_log
    where date_format(in_time,'%Y-%m')='2021-11'

一个表是新用户首次登陆情况表 new_user_t t2（date，uid）用min

    SELECT *
    from tb_user_log
    where (uid,in_time) in (
      select uid,min(in_time)
      from tb_user_log
      group by uid

将将t1 left join t2 然后group by t2的date（新用户首次登陆时间） 计算次日留存人数（用case when & count）和当日新用户人数（count）

select date(b.in_time) dt
,round(count(distinct case when datediff(a.in_time,b.in_time)=1 then b.uid end)/count(distinct b.uid),2) 次日留存率
from active_user_t a
inner join new_user_t b
on a.uid=b.uid
group by date(b.in_time)
order by dt