题解 | #2021年11月每天新用户的次日留存率#
2021年11月每天新用户的次日留存率
https://www.nowcoder.com/practice/1fc0e75f07434ef5ba4f1fb2aa83a450
select a.dt, round(count(b.uid) / count(a.uid), 2) as uv_rate
from (
select uid, min(date(in_time)) as dt
from tb_user_log
group by uid
) a
left join (
select distinct uid, date(in_time) dt
from tb_user_log
union
select distinct uid, date(out_time) dt
from tb_user_log
) b
on a.uid = b.uid
and a.dt = date_sub(b.dt, interval 1 day)
where date_format(a.dt, '%Y-%m') = '2021-11'
group by a.dt
order by a.dt asc
