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

select a0.date, 
ifnull(round(count(b.date)/count(a.date),3),0) # 如果为空，设置为0
from
(
# 所有日期
select date
from login
group by date
) a0
left join 
(
# 每个用户做为新用户的日期
select user_id, min(date) date
from login
group by user_id
) a on a0.date = a.date
left join
(
# 每个用户的所有登陆日期
select user_id, date
from login
order by user_id
) b on a.user_id = b.user_id and a.date = date_sub(b.date, interval 1 day)
group by a0.date

新的更好理解的思路