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

select
t1.dt as installDate,
round(count(distinct case when t2.dt=date_add(t1.dt,interval 1 day) then t2.uid end)/count(distinct t1.uid),2) as uv_left_rate
from
(
select
uid,min(date_format(in_time,'%Y-%m-%d')) dt -- 这里其实有个细节要注意，因为对象是每天新用户，增加min
from
tb_user_log 
group by uid
) t1
left join 
(
select
uid,date_format(out_time,'%Y-%m-%d') dt 
                               -- 这里其实有个细节要注意，测试用例有一个视频播放是跨天的，用out_time
from
tb_user_log 
) t2
on t1.uid=t2.uid 
where month(t1.dt)=11 
group by installDate;

这里给出一种通用的解法，求次日留存率，次七日留存都可以这样写

1. 将两张带有uid和时间的表通过左连接连接起来，连接条件只需要uid同
2. 在select部分用case when处理条件，次七日留存就这样写t2.dt<=date_add(t1.dt,interval 7 day)

如果有一张订单表order还能计算转化率和ITV，通过uid和时间左连接，order表和t2时间同

这是面试官教写的一种方法，应该比较类似于实际应用中的写法