统计牛客每个日期登录新用户个数

两种解法：

• 使用count计算每日新用户个数
• 使用case和窗口函数计算每个用户的登陆次数序号，选取第一次登陆的计数

使用count计算每日新用户个数时，要注意不能遗漏新用户个数为0的日期，可以使用union补充上

SELECT l.mdate AS date,COUNT(user_id) AS new
FROM (
    SELECT user_id,MIN(date) AS mdate
    FROM login
    GROUP BY user_id)AS l
GROUP BY l.mdate 
UNION 
SELECT date,0 AS new
FROM login
WHERE date NOT IN (
    SELECT MIN(date)
    FROM login
    GROUP BY user_id)
ORDER BY date

使用case和窗口函数计算

SELECT date,
SUM(CASE WHEN r=1 THEN 1 ELSE 0 END )AS new
FROM (
    SELECT user_id,date,
    DENSE_RANK() OVER (PARTITION BY user_id ORDER BY DATE) AS r
    FROM login) AS l
GROUP BY date
ORDER BY date