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

# 强调新用户，需要找到最早的登录日期，然后看第二天的
with
t1 as(
    select
        user_id,
        date,
        min(date)over(partition by user_id) as min_date
    from
        login
),
t2 as(
    select
        round(count(
            case
                when timestampdiff(day,min_date,date)=1 then 1
                else null
            end
        )/count(
            case
                when min_date=date then 1
                else null
            end
        ),3) as p
    from
        t1
)

select * from t2