审题：统计一下牛客每个日期登录新用户个数,即新用户在某天是第一次登录！

1、先找到每个用户第一次登录的日期

# select user_id, min(date) first_date from login group by user_id

2、再找到所有用户登录的唯一日期

# select distinct date from login

3、最后将两个日期表进行联结，分组统计日期出现的次数。

先查看下联结后的结果

select * from (select distinct date from login) a 
left join (select user_id, min(date) first_date from login group by user_id) b
on a.date = b.first_date;

2020-10-12|1|2020-10-12
2020-10-12|2|2020-10-12
2020-10-12|3|2020-10-12
2020-10-13|None|None
2020-10-14|4|2020-10-14
2020-10-15|None|None
分组统计用户ID即可：

select a.date, count(b.user_id) from (select distinct date from login) a 
left join (select user_id, min(date) first_date from login group by user_id) b
on a.date = b.first_date
group by a.date
order by a.date;