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

SELECT date, IFNULL(round(cr/new, 3), 0) FROM (SELECT date,sum(case when (user_id, date) in (select user_id, min(date) from login group by user_id) then 1 else 0 end) as new
from login
group by date
order by date) x
LEFT JOIN
(select DATE_SUB(date,INTERVAL 1 DAY) as date, count(distinct user_id) as cr 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) y
USING (date)