with cte as (
select
role_id,
install_date,
datediff(b.event_date,install_date) day_diff
from (
select
role_id,min(event_date) install_date
from role_login group by role_id
) a join role_login b using(role_id)
)
select
install_date,
sum(day_diff=0) 新增用户数,
sum(day_diff=1) 次日留存,
sum(day_diff=2) 2日留存,
sum(day_diff=3) 3日留存,
sum(day_diff=4) 4日留存,
sum(day_diff=5) 5日留存,
sum(day_diff=6) 6日留存,
sum(day_diff=7) 7日留存,
concat(round(sum(day_diff=1)*100/sum(day_diff=0),2),"%") 次日留存率,
concat(round(sum(day_diff=2)*100/sum(day_diff=0),2),"%") 2日留存率,
concat(round(sum(day_diff=3)*100/sum(day_diff=0),2),"%") 3日留存率,
concat(round(sum(day_diff=4)*100/sum(day_diff=0),2),"%") 4日留存率,
concat(round(sum(day_diff=5)*100/sum(day_diff=0),2),"%") 5日留存率,
concat(round(sum(day_diff=6)*100/sum(day_diff=0),2),"%") 6日留存率,
concat(round(sum(day_diff=7)*100/sum(day_diff=0),2),"%") 7日留存率
from cte where day_diff<=7
group by 1 order by 1;