注意：

when (user_id,date) in (select user_id,DATE_ADD(date,INTERVAL -1 DAY) from login group by user_id)then 1 else 0 end中的第一个date指的是DATE_ADD中的date加上INTERVAL -1 DAY，并不是DATE_ADD中的date,而且DATE_ADD中的date应限制为每个user_id下的倒数第二小date，本题能过测试，是因为测试例子不够丰富！

select date,
ifnull(round(
(sum(case when (user_id,date)
in (select user_id,DATE_ADD(date,INTERVAL -1 DAY) from login group by user_id)then 1 else 0 end)
)
/(sum(case when (user_id,date) in
# 找出每个用户的登录日
(select user_id,min(date)from login group by user_id)
then 1 else 0 end))
,3
)
,0) as p
from login
group by date
order by date;