SELECT
a.date,
ROUND(COUNT(b.user_id) * 1.0/COUNT(a.user_id), 3) AS p
FROM (
SELECT
user_id,
MIN(date) AS date
FROM login
GROUP BY user_id) a
LEFT JOIN login b
ON a.user_id = b.user_id
AND b.date = DATE_ADD(a.date,INTERVAL 1 DAY)
GROUP BY a.date
UNION
SELECT
date,
0.000 AS p
FROM login
WHERE date NOT IN ( -- 这是没有当日新用户(那么这天的次日转存率一定为0)
SELECT
MIN(date)
FROM login
GROUP BY user_id)
ORDER BY date;