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

#小白新思路，好理解，利用窗口函数直接求出结果。
#1.先合并表，因题目说了离开日期为第二天也算隔天登录。
#2.先找到第一次登录时间，因按照天计算，可以形成1天多次登录，第一天多次登录都算第一次登录时间。
#3.利用lead()求出下次登录时间，因要求次日留存率，故需要。
#4.筛出2021年11月份，排名第一次登录的时间，与lead()的结果差=1，就是隔天的人数count(distinct uid).
#5.相除就能得到答案。
with tiaojian as (
select 
uid,
date(in_time) as pday
from tb_user_log
union all
select
uid,
date(out_time) as pday
from tb_user_log
)

select 
t.pday,
round(
count(distinct case when datediff(t.y,t.pday)=1 then uid end)/
count(distinct uid),2) as uv_left_rate

from(
select 
uid,
pday,
lead(pday,1)over(partition by uid order by pday) as y,
dense_rank()over(partition by uid order by pday) as m
from tiaojian
) as t 
where
t.m=1
and
date_format(t.pday,"%Y%m")=202111
group by t.pday
order by t.pday