统计每个日期新用户的次日留存率

select date,
ifnull(
    round(sum(case when (user_id, date) in (
        select user_id, date_sub(date, interval 1 day)
        from login
        group by user_id
        )
        and (user_id, date) in (
            select user_id, min(date)
            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