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

select 
    whole.date,
    if(part.rate is null,0.000,part.rate)
FROM
(
    select distinct date
    from login
)whole
LEFT JOIN
(
    SELECT total.totDate,round(num.nu/total.tot,3) rate
    FROM 
    (
    SELECT totDate,COUNT(*) tot
    FROM
        (
        SELECT user_id,min(date) totDate FROM login GROUP BY user_id
        )tmp
    GROUP BY totDate
    )total
    JOIN
    (
    select date_add(date,INTERVAL -1 DAY) dateNum,COUNT(*) nu
    FROM login
    WHERE (user_id,date) in (select user_id,date_add(min(date),INTERVAL 1 DAY) from login group by user_id )
    GROUP BY date
    )num
    ON total.totDate = num.dateNum
)part
ON whole.date = part.totDate;