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

select it , 
    round(sum(case when pd1+pd2 >=1 then 1 else 0 end)/count(*),2)
from (
    select distinct
        t1.uid , 
        date_format(t1.in_time,'%Y-%m-%d') it,
        case when t2.id >0 then 1 else 0 end pd1, 
        case when t3.id >0 then 1 else 0 end pd2,
        case when t4.nd = date_format(t1.in_time,'%Y-%m-%d') then 1 else 0 end pd3
    FROM tb_user_log t1
    left join tb_user_log t2 
        on t1.uid = t2.uid and timestampdiff(day,t1.in_time,t2.in_time) =1
    left join tb_user_log t3
        on t1.id = t3.id and date_format(t3.out_time,'%Y%m%d')-date_format(t1.in_time,'%Y%m%d')>=1
    left join (
        select uid , date_format(min(in_time),'%Y-%m-%d') nd
        from tb_user_log 
        group by uid) t4
        on t1.uid = t4.uid) t5
where pd3 >0 and left(it,7) = '2021-11'
group by 1
order by 1

首先我是一个小白，下面是我做这题的心得：

说实话，有些函数的结果跟预想的不同，这让人非常头疼……

譬如in_time是23:59分 跟out_time 00:01 差2分钟，从日期上来看刚好跨越了一个自然日。那timestampdiff(day , in_time , out_time) 按理来说应该是返回1的，可是返回的居然是0，真的头痛。为了找出这个误差，花了很长的时间！！