SQL求每个日期登录新增用户个数

测试数据

drop table if exists login;
CREATE TABLE `login` (
`user_id` int(4) NOT NULL,
`date` date NOT NULL
);

INSERT INTO login VALUES
(2,'2020-10-12'),
(2,'2020-10-12'),
(3,'2020-10-12'),
(1,'2020-10-12'),
(2,'2020-10-13'),
(1,'2020-10-13'),
(3,'2020-10-14'),
(4,'2020-10-14'),
(4,'2020-10-14'),
(4,'2020-10-15');

select * from login ORDER BY user_id;

方法一

去重作为结果temp_0(防止用户一天登录多次)

select DISTINCT user_id,date from login order by user_id

看思路:直接上码

select distinct date,  
   sum(
      case when (user_id,date) in (select user_id,min(date) from login group by user_id) -- 求出用户和他最早的登录日期 
      then 1 else 0 end
   ) as new
    
from (select DISTINCT user_id,date from login order by user_id) temp_0 -- 去重
group by date
order by date

方法二

利用窗口函数rank()

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