SQL9 2021年11月每天新用户的次日留存率

题目地址
挺不错的一个题，把几个知识点都用到了，主要用到的知识点有

• count(*)over
• UNION
• left join

用 UNION 将 in_time 和 out_time 看作同一列，顺便去重，count(*)over 判断新出现节点，left join 利用其不满足 on 条件就为 NULL 的性质找出次日留存的项。
另外，With as 算是一个比较好的技巧，免得各种嵌套把自己整晕了。
顺便附一个 left join 的博客

With base as(
select uid,dt,
    count(*)over(partition by uid order by dt) as times
from(
    select uid,
        date_format(in_time,'%Y-%m-%d') as dt
    from tb_user_log
    UNION
    select uid,
        DATE_FORMAT(out_time,'%Y-%m-%d') as dt
    from tb_user_log
    )tmp
)

select now.dt,
    round(count(nxt.dt)/count(*),2) as uv_left_rate
from base now
left join base nxt
on now.uid=nxt.uid and nxt.dt=timestampadd(day,1,now.dt)
where now.times=1 and date_format(now.dt,'%Y-%m')='2021-11'
group by dt
order by dt;