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

-- 思路是分别求出首次登录数和次日登录数
-- 首次登录数就是一共有几个独立访客
-- 次日登录数就是登录日期 - 首次登录日期 = 1的独立访客数
select
    round(
        count(
            distinct case
                when datediff (t2.date, t1.first_date) = 1 then t1.user_id
            end
        ) / count(distinct t2.user_id),
        3
    ) as p
from
    (
        select
            user_id,
            min(date) as first_date
        from
            login
        group by
            user_id
    ) as t1
    join login as t2 on t1.user_id = t2.user_id;