题解 | #日活次日留存率和新户次日留存率# 小白复杂写法

with date as (
select 
uid,
min(login_date) as first_date
from user_login_tb
group by uid
),date1 as (
select 
uid,
login_date 
from user_login_tb
group by uid,login_date
),tiaojian as (
select 
t.first_date,
round(
sum(case when diff=1 then 1 else 0 end)/
sum(case when diff=0 then 1 else 0 end),2)  as new_uv_left_rate
from(
select 
d.first_date,
d1.login_date,
d1.uid,
datediff(d1.login_date,d.first_date) as diff
from date d left join date1 d1 on d.uid=d1.uid
) as t 
group by t.first_date
),tiaojian1 as (
select 
t.login_date,
round(
sum(case when datediff(t.m,t.login_date)=1 then 1 else 0 end)/
count(distinct uid),2) as uv_left_rate
from(
select 
login_date,
uid,
lead(login_date,1)over(partition by uid order by login_date) as m 
from user_login_tb
) as t 
group by t.login_date
)
 
select 
t1.login_date,
t1.uv_left_rate,
t.new_uv_left_rate
from tiaojian1 t1 left join tiaojian t 
on t1.login_date=t.first_date