-- 日期表
with t0 as
(
    select date
    from login
    group by date
),
-- 首次登入日期表
t1 as 
(
    select distinct user_id, min(date) as date
    from login
    group by user_id
)

-- 计算次日留存率
select 
    t0.date,
    ifnull(round(count(t2.date) / count(t1.date),3),0) as retention_rate
from t0 
left join t1 on t0.date = t1.date
left join login as t2 on t1.user_id = t2.user_id and date_add(t1.date,interval 1 day)=t2.date
group by t0.date
order by t0.date;