题解 | #牛客每个人最近的登录日期(五)#
牛客每个人最近的登录日期(五)
https://www.nowcoder.com/practice/ea0c56cd700344b590182aad03cc61b8
select t2.date, case when sum(t2.first_login) = 0 then 0.000 else round(sum(case when (t2.user_id,t2.add_day) in (select user_id,date from login) and t2.first_login = 1 then 1 else 0 end)/sum(t2.first_login),3) end p from (select *, case when (t1.user_id,t1.date) in (select user_id,min(date) from login group by user_id) then 1 else 0 end first_login, date_add(t1.date,interval 1 day) add_day from login t1 ) t2 group by t2.date order by t2.date