新用户注册时间

with A as ( select user_id,min(date) as date from login group by user_id )

假设第一天新用户登录数 上一题做过 直接粘贴

,B as ( select date,count(user_id)new from ( select distinct login.date,a.user_id from login left join (select user_id,min(date)date from login group by 1)a on login.date = a.date )b group by 1 )

统计第二天还登录的新用户

,C as ( select date,count(user_id)new2 from A where (user_id,date_add(date,interval 1 day)) in ( select user_id,date from login ) group by 1 )

表B,C左连接一下 一除就出来了,这里要注意 除数为0时 赋值为1

select B.date ,round(ifnull(new2,0)/if(new=0,1,new),3)p from B left join C on B.date = C.date