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

SELECT
    a.date,
    round(IFNULL(((
        select
            count(*)
        from
            login b
        where
            b.date = (
                select date_add(a.date, interval 1 day)
            )
            and b.user_id in (
                select
                    f.user_id
                from
                    login f
                where
                    f.user_id not in (
                        select
                            g.user_id
                        from
                            login g
                        where
                            g.date < a.date
                    )
                    and f.date = a.date
            )
    ) / (
        select
            count(*)
        from
            login e
        where
            e.user_id not in (
                select
                    d.user_id
                from
                    login d
                where
                    d.date < a.date
            )
            and e.date = a.date
    )),0),3)
FROM
    login a
group by
    a.date