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

SELECT c.ndate,
CASE
WHEN d.new IS NULL THEN 0
ELSE d.new
END
FROM (
SELECT DISTINCT(date) AS ndate
FROM login
) AS c LEFT JOIN (
/1.如果当前用户id不在当前日期之前的登录记录中出现过，那就判定该用户id是当前日期的新用户，
然后按照日期分组，求出各日期中新登录用户的数量
*/
SELECT a.date,COUNT() AS new
FROM login AS a
WHERE a.user_id NOT IN (
SELECT b.user_id
FROM login AS b
WHERE a.date>b.date
)
GROUP BY a.date
) AS d
ON c.ndate=d.date
ORDER BY c.ndate