题解 | #2021年11月每天新用户的次日留存率#
2021年11月每天新用户的次日留存率
http://www.nowcoder.com/practice/1fc0e75f07434ef5ba4f1fb2aa83a450
小白 实际工作中的思路做法，请大牛们多多指教。
利用，虚拟表把每位UID最早的登录时间查出来（表1），在用虚拟表把所有信息时间保存出来，因为有一条件24时之后也算之前的活跃度，所以要考虑到end所以2个时间合并，且重复不合并用union
之后，取出最早时间，用户登录时间，时间差求出来，在相除即可
with date1 as
(select uid,min(date(in_time)) as first_time from tb_user_log
group by uid
)
,
date2 as
(
select uid ,date(in_time) as sdate from tb_user_log
union
select uid ,date(out_time) as sdate from tb_user_log
)
select
t1.first_time,
round(
day1/day0,2)
from
(
select
t.first_time,
sum(case when t.diff=0 then 1 else 0 end) as day0,
sum(case when t.diff=1 then 1 else 0 end) as day1
from(
select
date1.first_time,
date2.sdate,
date2.uid,
datediff(date2.sdate,date1.first_time) as diff
from date1 left join date2 on date1.uid=date2.uid
) as t
where
t.first_time like "2021-11%"
group by t.first_time
) as t1
order by t1.first_time
