题解 | #日活次日留存率和新户次日留存率#

select
    ta.login_date,
    uv_left_rate,
    new_uv_left_rate
from
(select t1.login_date, round(count(t2.id) / count(t1.id), 2) as uv_left_rate
 from user_login_tb t1
          left join user_login_tb t2 on t1.uid = t2.uid and t1.login_date + 1 = t2.login_date
 group by t1.login_date
) ta
    left join
    (
select
    md,
    round(count(t2.uid)/count(t1.uid),2) as new_uv_left_rate
from
    (select uid,min(login_date) md from user_login_tb group by uid) t1
left join user_login_tb t2
on t1.uid =t2.uid and md+1 = t2.login_date
group by t1.md
    ) tb
on login_date = md