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

with a2 as
(with a1 as (select uid,in_time,out_time
from tb_user_log
)
select distinct a.uid,date(a.in_time) date,date(b.in_time) 2_date
from a1 a left join a1 b
on (date_add(date(a.in_time),interval 1 day) = date(b.in_time)
or date_add(date(a.in_time),interval 1 day) = date(b.out_time))
and a.uid = b.uid
where date(a.in_time) like '2021-11%')
select date,round(count(2_date)/count(date),2) uv_left_rate
from a2
where (uid,date) in 
(
with a3 as (
with a2 as(
with a1 as (select uid,in_time,out_time
from tb_user_log
)
select distinct a.uid,date(a.in_time) date,date(b.in_time) 2_date
from a1 a left join a1 b
on (date_add(date(a.in_time),interval 1 day) = date(b.in_time)
or date_add(date(a.in_time),interval 1 day) = date(b.out_time))
and a.uid = b.uid)
select *,
rank()over(partition by uid order by date) ranking 
from a2)
select uid,date from a3
where ranking = 1
and date like '2021-11%' 
)
group by date
order by date;