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

# # 步骤一，找出每个用户的初次登录日期
# select user_id, min(date) as min_date
# from login
# group by user_id;

# 步骤二，查询每个日期登录新用户个数，并且查询结果按照日期升序排序
select login.date,
sum(case when login.date = t1.min_date then 1 else 0 end) as new
from login
left join (
    # 步骤一，找出每个用户的初次登录日期
    select user_id, min(date) as min_date
    from login
    group by user_id) as t1 using(user_id)
group by date
order by date;