解法一
1、查询所有的日期
select distinct date from login2、查询每个用户的首次登录日期
select user_id,min(date) as date from login group by user_id3、将以上两个表格左连接,以date分组、用case when函数统计个数
select a.date, sum(case when b.user_id is null then 0 else 1 end) as new from (select distinct date from login) as a left join (select user_id,min(date) as date from login group by user_id) as b on a.date=b.date group by a.date order by a.date;
解法二
窗口函数
1、在原表中增加一列每个用户的第一次登录日期
select user_id,date,min(date) over(partition by user_id) as d from login2、1表中进行查询,如果date=d 返回1,不等于返回0 ;对date进行分组求和
select date,sum(case when date=d then 1 else 0 end) as new from (select user_id,date,min(date) over(partition by user_id) as d from login) as a group by date order by date;