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

select m.date,IFNULL(ROUND(j.next_cn/n.new_cn,3),0) FROM 
(select distinct date from login) m left join 
(select t.date ,count(t.date) new_cn from (select user_id,min(date) date  from login group by user_id) t  group by t.date) n on m.date =n.date
left join 
(select DATE_ADD(p.next_date,INTERVAL -1 DAY)  date, p.next_date,p.next_cn from 
((select k.next_date,count(k.next_date) next_cn from 
(select a.user_id,b.date,b.next_date  from login a join 
(select user_id,min(date) date, DATE_ADD(min(date),INTERVAL 1 DAY) next_date from login group by user_id) 
b on a.user_id=b.user_id and a.date =b.next_date) k group by k.next_date)) p) j on j.date=m.date 
ORDER BY m.date

你别问怎么写成了石山语句，你就说能不能运行成功吧，哈哈哈哈哈