利用sql求N日留存率，新用户数，用户最大登录天数

#七日留存率
select a.dates,concat(round(count(distinct if(datediff(b.dates,a.dates)=7,b.user_id,null))/count(distinct a.user_id),2)*100,'%')as '七日留存率'
from
(select user_id,dates from user group by user_id, dates) a
left join
(select user_id,dates from user group by user_id, dates) b
on a.user_id=b.user_id and datediff(b.dates,a.dates)=7
group by  a.dates;

#每个用户的最大登录天数
select * from user_login;
select user_id,max(conitious)
from
(select user_id,date_sub(date_login,interval rk day) as date_sub,
       count(date_sub(date_login,interval rk day)) as conitious
from
(select *,row_number() over (partition by user_id order by date_login) as rk from user_login) a
group by user_id,date_sub(date_login,interval rk day)) b
group by user_id;

#当天的新增用户
select b.min_date,count(user_id)
from
(
    select user_id, min(dates) as min_date
    from user
    group by user_id
) b
group by b.min_date