开窗函数 | #牛客每个人最近的登录日期(三)#

select round(
    (
        select count(*) #次日成功留存的新用户的数量
        from (
            select * 
                ,min(date) over (PARTITION by user_id) as first_day
            from login
        ) as temp1 
        where date = DATE_ADD(first_day,INTERVAL 1 day)
    )
    / 
    (SELECT count(DISTINCT user_id) 
    from login )
    ,3
    )
    as p