7日留存率计算

• 1.构建临时表 id， 登录日期，注册日期
• 2.求登录日期-注册日期大于7

with t1(
select id,login_date, b.resgister_date,date_diff(login,resgister_date) as rk
from (select distinct id, login_Date from a1) a
left join (select id,min(date) as resgister_date  from b1 group by id)b
) on a.id=b.id

select login_Date
sum(case when rk>=7 then 1 else 0 )/count(distinct(id)) as remain_7
 from t1 
where 
group by login_Date