题解 | #牛客每个人最近的登录日期(四)#
牛客每个人最近的登录日期(四)
https://www.nowcoder.com/practice/e524dc7450234395aa21c75303a42b0a
select g.date,ifnull(new,0)
from
(select e.date,f.new
from
(select distinct date from login)e
left join
(select b.date,count(b.ra) new
from
(select a.* from
(select user_id,client_id,date, rank() over(partition by user_id order by date) ra from
login)a
where a.ra=1) b
group by b.date)f
on e.date=f.date)g order by date;
from
(select e.date,f.new
from
(select distinct date from login)e
left join
(select b.date,count(b.ra) new
from
(select a.* from
(select user_id,client_id,date, rank() over(partition by user_id order by date) ra from
login)a
where a.ra=1) b
group by b.date)f
on e.date=f.date)g order by date;
