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

with a as(
    select user_id
    ,min(date) as reg_date
    from login
    group by user_id
)

select 
round(count(distinct case when datediff(b.date,a.reg_date)=1 then b.user_id else NULL end)/count(distinct a.user_id),3) as p
from a left join login b on a.user_id=b.user_id

1、注意输出形式，保留3位小数是round，如果要用百分比的形式，要改成concat（round（x,3）*100,'%')这样子

2、count(distinct case when datediff(b.date,a.reg_date)=1 then b.user_id else NULL end)/count(distinct a.user_id)用这种形式求留存率非常方便而且求几日改成几就ok了