题解 | #牛客每个人最近的登录日期(四)#

select distinct l.date , case when nt.new is not null then nt.new else 0 end as new from login as l left join ( select distinct date,count(user_id) over(partition by date) as new from login where (user_id , date) in ( select user_id , min(date) from login group by user_id) ) as nt on l.date = nt.date order by l.date; #1.找出每个用户第一天登录的时间 select user_id , min(date) from login group by user_id #2.使用子查询找出按照date分组计算得到的新用户数量 select distinct date,count(user_id) over(partition by date) as new from login where (user_id , date) in ( select user_id , min(date) from login group by user_id #3.第二步会忽略掉当天没有新用户登录的日期，那么就需要和原表进行左连接。找出没有新用户登录的日期，那么它对应的new列就是null值，再利用case表达式将null值转换为0。 select distinct l.date , case when nt.new is not null then nt.new else 0 end as new from login as l left join ( select distinct date,count(user_id) over(partition by date) as new from login where (user_id , date) in ( select user_id , min(date) from login group by user_id) ) as nt on l.date = nt.date order by l.date;