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

1.获得每个用户的第一次登陆时间
select user_id,min(date) first_date
from login
group by user_id;t1
2.用t1获得每个登陆时间的次数，即该登陆时间的新增用户
select distinct first_date,count(first_date) new
from (select user_id,min(date) first_date
from login
group by user_id)t1
group by first_date;t2
3.用t2与原表右连接，以原表出现的日期为第一列，第二列查找t2中对应日期的新增用户，用IFNULL判断，如果查不到取0，最后根据原表的日期进行排序
select distinct login.date,ifnull(new,0)
from (select distinct first_date,count(first_date) new
from (select user_id,min(date) first_date
from login
group by user_id)t1
group by first_date)t2
right join login
on login.date=t2.first_date
order by login.date