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

select a1.date, ifnull(round(c1/c2,3),0) from
(select r1.date, c1 from
	(select t.date, count(l.user_id) c1 from 
		(select r1.date, r2.user_id from (select distinct date from login) r1 
 left join (select user_id, min(date) date from login group by user_id) r2 
	on r1.date=r2.date) t 
	left join login l on t.user_id=l.user_id
	where l.date=date_add(t.date, interval 1 DAY)
	group by t.date) r2
right join (select distinct date from login) r1 on r2.date=r1.date) a1
join (select l1.date, count(l2.user_id) c2 from (select distinct date from login) l1
left join (select user_id, min(date) date from login group by user_id) l2
on l1.date=l2.date
group by l1.date) a2 
on a1.date=a2.date;