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

select 
	in_date as dt, 
	round(count(if(previous_date is null,if(in_date+interval 1 day=next_date ,1,if((DATE_ADD(in_date,interval 1 day))=out_date ,1,null)),null))/count(if(previous_date is null,1,null)),2) as uv_left_rate
from
(
	select 
		distinct uid,
		date(in_time) in_date,
		date(out_time) out_date,
		lag(date(in_time)) over(partition by uid order by in_time) previous_date,
		lead(date(in_time)) over(partition by uid order by in_time) next_date
	from tb_user_log t 
	order by in_date
) t
where month(in_date)=11
group by in_date
having count(if(previous_date is null,1,null))> 0