题解 | #牛客每个人最近的登录日期(四)#
牛客每个人最近的登录日期(四)
https://www.nowcoder.com/practice/e524dc7450234395aa21c75303a42b0a
SELECT DISTINCT
l.date,
CASE
WHEN s2.new IS NULL THEN 0
ELSE s2.new
END AS new_users
FROM login l
LEFT JOIN (
SELECT
s1.date,
COUNT(s1.user_id) AS new
FROM (
SELECT
user_id,
MIN(date) AS date
FROM login
GROUP BY user_id
) s1
GROUP BY s1.date
) s2 ON l.date = s2.date
先计算新用户及注册日期，然后右连distinct date，把没有新用户注册的日子标为0
查看23道真题和解析
不建议写那么大，可以从小出发更容易