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

明确题意：

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

问题分解：

查询每天的新用户：select uid,date(min(in_time)) dt from tb_user_log group by uid

查询每天的活跃用户：select uid,date(in_time) dt from tb_user_log union select uid,date(out_time) dt from tb_user_log

关联新用户表和活跃用户表：t1 left join t2 on t1.uid = t2.uid and t1.dt = date_sub(t2.dt, interval 1 day)

计算次日留存率：count(t2.uid) / count(t1.uid)

筛选2021年11月的记录：where date_format(t1.dt,'%Y-%m') = '2021-11'

按天分组：group by dt

细节问题：

保留2位小数：round(x,2)

按日期升序：order by dt

select
    t1.dt,
    round(count(t2.uid) / count(t1.uid), 2) uv_left_rate
from(
select 
    uid,
    date(min(in_time)) dt
from tb_user_log
group by uid
) t1
left join 
(
select 
    uid,
    date(in_time) dt
from tb_user_log
union
select 
    uid,
    date(out_time) dt
from tb_user_log
) 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 dt
order by dt;