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

SELECT l3.date
        ,round(ifnull((SELECT COUNT(*)
          FROM (SELECT l.user_id,DATE_ADD(l.date,
                                       interval -1 day) date
                FROM login l
                WHERE (l.user_id,l.date) in 
                      (SELECT l2.user_id,DATE_ADD(min(date),
                                       interval 1 day) date
                       FROM login l2
                       GROUP BY l2.user_id)
             ) g
          WHERE l3.date = g.date)
        / (SELECT COUNT(d)
           FROM login l4 left join (
                SELECT user_id,min(date) d
                FROM login
                GROUP BY user_id
                ) m on l4.user_id=m.user_id and l4.date=m.d
           WHERE l3.date = l4.date
           GROUP BY date),0),3)  p
FROM login l3
GROUP BY l3.date

代码都是借用前面几题的