SQL165大厂面试真题

统计新用户次日留存率

select t1.dt, round(count(t2.uid)/count(t1.uid), 2)uv_rate from

(

    select uid, min(date(in_time))dt from tb_user_log

    group by uid

)as t1 left join

(

    select uid, date(in_time)dt from tb_user_log union

    select uid, date(out_time) from tb_user_log

)as t2

on t1.uid = t2.uid and t1.dt = date_sub(t2.dt, interval 1 day)

where date_format(t1.dt, '%Y-%m') = '2021-11'

group by t1.dt

order by t1.dt