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

with a as (
	select
		uid,date(min(in_time)) dt
	from
		tb_user_log
	where
		uid not in (select uid from tb_user_log where in_time < '2021-11-01' or out_time < '2021-11-01')
	group by
		uid
),
b as 
(
	select
		uid,
		date(in_time) dt
	from
		tb_user_log
	union
	select
		uid,
		date(out_time) dt
	from
		tb_user_log
)
select 
	a.dt dt,
	round(count(b.uid)/count(*),2) uv_left_rate
from 
	a
	left join
	b
	on
	a.uid=b.uid
	and a.dt=b.dt - interval 1 day
group by
	a.dt
order by
	dt
;