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

SELECT ll.date,IFNULL(ROUND(tem.zi_count/ll.mu_count,3),0) AS p
  FROM
((SELECT l.date,SUM(IF(l.user_id NOT IN(SELECT DISTINCT user_id FROM login 
                                  WHERE date<l.date),1,0)) AS mu_count -- 这里目的是求新用户的,这里卡了很久,我太菜了
    FROM login l
   GROUP BY date) ll  -- 这里求出分母
 LEFT JOIN 
(SELECT a.date,COUNT(*) AS zi_count
   FROM login a
   JOIN login b ON a.user_id = b.user_id AND DAY(b.date) - DAY(a.date)=1
  GROUP BY a.date) tem
 ON tem.date = ll.date) -- 这里是求出分子
ORDER BY ll.date

做了很久才做出来,而且效率也不高,但是还是挺欣慰的