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

跟前面的题目相同，首先求出新用户的数量

select l1.date,count(l2.user_id)
from login l1 left join 
(select user_id,min(date) min_date from login group by user_id) l2
on l1.user_id = l2.user_id and l1.date = l2.min_date
group by l1.date
order by l1.date

结果如图所示

select l1.date,round(IFNULL(count(distinct l3.user_id)/count(distinct l2.user_id),0),3)
from login l1 left join 
(select user_id,min(date) min_date from login group by user_id) l2
on l1.user_id = l2.user_id and l1.date = l2.min_date
left join login l3 on l3.date = date_add(l2.min_date,interval 1 day) and l2.user_id = l3.user_id
group by l1.date
order by l1.date

即可求出答案，该题的坑点在于count(distinct l3.user_id)，count(distinct l2.user_id)都有可能为null，所以加个ifnull是必要的