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

with a as(
 select user_id
 ,min(date) as date
 from login
 group by user_id   
)

select l.date
,count(distinct case when l.date =a.date then l.user_id else NULL end) as new
from login l left join a on l.user_id=a.user_id
group by l.date
order by l.date

一开始是用where在login中筛选出所有user_id和min（date）的数据，然后再group by+count，后来发现跟答案不一样，只剩新用户不为0的日期了。。所以后面又调整了下，话说count+distinct case when的小连招真的很好用欸