题解 | #2021年11月每天新用户的次日留存率#
2021年11月每天新用户的次日留存率
http://www.nowcoder.com/practice/1fc0e75f07434ef5ba4f1fb2aa83a450
-- 对原表进行处理，因为涉及到跨天活跃：
with a as
(select uid,dt
from (
SELECT uid,date(in_time) dt from tb_user_log
UNION ALL
SELECT uid,date(out_time) dt from tb_user_log)t
group by uid,dt) -- 分组是为了去重
-- 分组后用sum(if())处理满足条件的数据：
-- 1.每天的新用户可以用每个用户的最小时间即最早时间：(uid,dt) in (SELECT uid,min(dt) from a group by uid)
-- 2.每天新用户的次日留存：满足1的同时，再加上当天的新用户在第二天中有即可：(uid,dt) in (SELECT uid,DATE_SUB(dt,INTERVAL 1 day) from a)
select * from (
select dt,round(sum(if((uid,dt) in (SELECT uid,min(dt) from a group by uid) and (uid,dt) in (SELECT uid,DATE_SUB(dt,INTERVAL 1 day) from a),1,0))/sum(if((uid,dt) in (SELECT uid,min(dt) from a group by uid),1,0)),2) uv_left_rate
from a
where DATE_FORMAT(dt,'%Y-%m')='2021-11'
group by dt)b
where uv_left_rate is not null
order by dt
