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

这题和这个https://blog.nowcoder.net/n/790154f3ad084dcfbf6042b42a8dc99f 找新增用户数很像。 本打算如下。但是下面的代码不会统计出新增用户数为0的日期，和题目的答案不相符。

SELECT date, COUNT(user_id) new FROM
(SELECT user_id,MIN(date) date FROM login GROUP BY user_id) t1
GROUP BY date
ORDER BY date

之后参考了讨论区，用上了CASE WHEN (user_id, date) IN...然后SUM (法一）。

SELECT date,SUM(num) new FROM
(SELECT date,
 (CASE WHEN (user_id, date) IN (SELECT user_id,MIN(date) date FROM login GROUP BY user_id)
 THEN 1 ELSE 0 END) num
 FROM login
) t1
GROUP BY date
ORDER BY date

法二，根据用户分组，记录rank等于1的，然后求和计数。

SELECT date, SUM(CASE WHEN rk=1 THEN 1 ELSE 0 END) new
FROM (SELECT date, rank() over (PARTITION BY user_id ORDER BY date) rk FROM login) t1
GROUP BY date
ORDER BY date