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

select date,count(t1.md) from login left join 
(select user_id,min(date)md from login group by user_id)t1 
on t1.user_id=login.user_id and t1.md=login.date
group by date
order by date

其他方法1：

select date
    ,count(distinct case when (user_id,date) in
(select user_id,min(date)from login group by user_id)
then user_id else null end)
from login
group by date
order by date;

其他方法2：

select a.date, count(b.user_id) new
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