题解 | #牛客每个人最近的登录日期(四)#
牛客每个人最近的登录日期(四)
http://www.nowcoder.com/practice/e524dc7450234395aa21c75303a42b0a
# # 步骤一,找出每个用户的初次登录日期
# 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;