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

#第一遍做：成功
   #select distinct date, sum(case when d=date then 1 else 0 end)
   #from
   #(
     # select l.user_id as user_id,l.date as date,t.d as d
      #   from login l
       #  left outer join
      #   (select distinct user_id,min(date) as d from login group by user_id) t
       #  on l.user_id=t.user_id and l.date=t.d
   #) as ta
  # group by date
   #order by date

 #第二遍做,思路更清晰了
    select distinct d1,count(user_id)
    from
    (select distinct date d1 from login) a
    left outer join
    (
    select user_id,min(date) d2 from login group by user_id
    ) b
    on a.d1=b.d2
    group by d1
    order by d1