审题:统计一下牛客每个日期登录新用户个数,即新用户在某天是第一次登录!
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;