题解 | #牛客每个人最近的登录日期(五)#
牛客每个人最近的登录日期(五)
http://www.nowcoder.com/practice/ea0c56cd700344b590182aad03cc61b8
select date, round(IFNULL(sum(case when
(user_id, date) in (select user_id, min(date) from login group by user_id) and
(user_id, date+1) in (select user_id, date from login)
then 1 else 0 end)/
sum(case when
(user_id, date) in (select user_id, min(date) from login group by user_id)
then 1 else 0 end),0),3)
from login
group by date
order by date
(user_id, date) in (select user_id, min(date) from login group by user_id) and
(user_id, date+1) in (select user_id, date from login)
then 1 else 0 end)/
sum(case when
(user_id, date) in (select user_id, min(date) from login group by user_id)
then 1 else 0 end),0),3)
from login
group by date
order by date