SELECT DISTINCT a.date, IFNULL(ROUND(new_cust.new_count/new_stay.new_stay_count,3),0) as p from login a left JOIN( select date,count(1) as new_stay_count from ( select date,user_id from ( SELECT a.user_id,a.date,b.user_id as bid,b.date as bdate from login a left JOIN login b on b.user_id=a.user_id and a.date>b.date )a where a.bid is NULL )a GROUP BY date )new_stay on a.date=new_stay.date left join ( SELECT new_cust.date,count(new_cust.user_id) as new_count from ( select date,user_id from ( SELECT a.user_id,a.date,b.user_id as bid,b.date as bdate from login a left JOIN login b on b.user_id=a.user_id and a.date>b.date )a where a.bid is NULL )new_cust INNER JOIN login b on DATE_ADD(new_cust.date ,INTERVAL 1 DAY)=b.date and new_cust.user_id=b.user_id GROUP BY new_cust.date )new_cust on new_cust.date=new_stay.date ORDER BY date