SQL 69) 查询每个日期登录新用户个数

方法）
一开始是这样写的

SELECT date, COUNT(user_id)
FROM login
WHERE (date, user_id) IN(
    SELECT MIN(date), user_id
    FROM login
    GROUP BY user_id
)
GROUP BY date;

但没办法通过，因为无法输出没有新用户登录的日期

• 因此要外连结一个完整的login作为主表

  SELECT l.date, IFNULL(a.new_num, 0)
  FROM login as l
  LEFT JOIN( 
    SELECT date, COUNT(user_id) AS new_num
    FROM login
    WHERE (date, user_id) IN(
    SELECT MIN(date), user_id
    FROM login
    GROUP BY user_id)
  GROUP BY date)a
  ON a.date = l.date
  GROUP BY l.date
  ORDER BY l.date;

• 借鉴大神的窗口函数解法

  select a.date,
  sum(case when t_rank=1 then 1 else 0 end) new
  from 
  (select date, row_number() over(partition by user_id order by date) t_rank
  from login) a
  group by date;