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

法一：

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

法二：（推荐）

select
    date,
    ifnull(round(sum(if(rk=1 and datediff(ld_dt,date)=1, 1, 0)) / sum(if(rk=1, 1, 0)), 3), 0) as p
from (
    select
        user_id,
        date,
        row_number()over(partition by user_id order by date) as rk,
        lead(date, 1)over(partition by user_id order by date) as ld_dt
    from (
        select
            distinct user_id, date
        from login
    ) t
) tt
group by date
order by date