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

select t.date, ifnull(round(count(distinct b.user_id)/count(distinct a.user_id),3),0) as p
from  
(select date from login group by date)t

left join

(select user_id,min(date) as first_date from login
group by user_id)as a 
on t.date = a.first_date

left join  login as b
on a.user_id = b.user_id
and b.date=date_add(a.first_date,interval 1 day)

group by t.date 

没什么思路，但是看别人的答案后发现这题和 https://www.nowcoder.com/practice/16d41af206cd4066a06a3a0aa585ad3d?tpId=82&tags=&title=&difficulty=0&judgeStatus=0&rp=1&sourceUrl=%2Fexam%2Foj%3FquestionJobId%3D2%26subTabName%3Donline_coding_page

很像，在链接中题目的基础上多链接了一张表t，这样最后group by就可以用t.date实现，否则如果用a.first_date的话，会出现某个日期的值是none的情况