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

/*
with a as(
    select 
        user_id,
        min(date) min_date
    from login 
    group by user_id
),
b as(
    select distinct date from login
),
new_user as (
        select b.date,user_id
        from b left join a on b.date=a.min_date
),
count_newuser as(
    select date,count(user_id) newuser_cnt
    from new_user
    group by date
),
t2 as(
    select user_id,date_sub(min(date),interval 1 day) date_mark
    from login
    where (user_id,date) not in (
        select user_id,min(date) from login
        group by user_id
    ) 
    group by user_id
),
newuser_next as(
    select 
        date_mark date,
        user_id
    from t2
    where (user_id,date_mark) in (select user_id,date from new_user)
),
count_newuser_next as(
    select date,
        count(user_id) nextnewuser_cnt
    from b
    left join newuser_next using(date)
    group by date
)

select date,
    coalesce(round(nextnewuser_cnt/newuser_cnt,3),0) p
from count_newuser
left join count_newuser_next using(date)
*/
with newuser_in_nextdate as(
    select 
        a.date,
        b.user_id newuser,
        c.user_id newuser_next,
        c.date date_next
    from (select distinct date from login) a
    left join (select user_id,min(date) min_date from login
            group by user_id) b
    on a.date=b.min_date
    left join login c 
    on b.user_id=c.user_id and datediff(c.date,a.date)=1
)

select 
    date,
    coalesce(round(count(newuser_next)/count(newuser),3),0) p
from newuser_in_nextdate
group by date

/*coalesce(column,0) 若列值为空值，替换为0；若不为空值，就取原值。
*/