题解 | 牛客每个人最近的登录日期(五)

# 统计牛客每个日期新用户的次日留存率
with new_login AS
(
select user_id,min(date) as new_date
from login
group by user_id
),
riqi AS
(
select distinct date
from login
)

select a0.date as date,
case when count(distinct a1.user_id)=0 then 0.000
else round(count(distinct a2.user_id)/count(distinct a1.user_id),3)
end as p
from 
riqi as a0 left outer join
new_login as a1 on a0.date=a1.new_date
left outer join
login as a2 on a0.date=date_sub(a2.date,interval 1 day)
and a1.user_id=a2.user_id
group by a0.date
order by a0.date asc