题解 | #牛客每个人最近的登录日期(五)#
牛客每个人最近的登录日期(五)
http://www.nowcoder.com/practice/ea0c56cd700344b590182aad03cc61b8
with t as (
select user_id,date,lead(date,1)over(partition by user_id) as dtime
from login
# where (user_id,date) in (select user_id,min(date) from login group by user_id)
)
select distinct t2.date,
case when rate is not null then rate
else 0.000
end as p
from
(select date,round(sum(datediff(dtime,date)=1)/count(1),3) as rate
from t
where (user_id,date) in (select user_id,min(date) from login group by user_id)
group by date) t1
right join login t2 on t1.date = t2.date
查看16道真题和解析
