casewhen+窗口函数解法

面试后得到反馈说，因为笔试一直在用子查询跟join虽然对了但不好，所以要加试。在加试之前回来把刷过的题都练习着改一遍，尽量用case when跟窗口函数代替子查询跟join。
以下是我的尝试：

select date, 
ifnull(round(sum(case when date=min_date and datediff(next_date,date)=1 then 1 else 0 end) / sum(case when date=min_date then 1 else 0 end),3),0) as p
from(
    select user_id, date, min(date) over (partition by user_id) as min_date, lead(date,1) over(partition by user_id order by date) as next_date
    from login
) a
group by date
order by date;

（刚接触sql几天，不是很熟悉，虽然过了用例，可能仍存在bug，请多指教。