留存计算

下例根据20180605号的登录计算历史N天留存，本例未加注册时间限制

select a.DateID,a.platid,DATEDIFF(cast(c.DateID as datetime),cast(a.DateID as datetime)),count(distinct a.Accid)

from Account_3004 a inner JOIN RoleInfo_3004 b
on a.Accid=b.accid and a.platid=b.platid
inner join LoginoutLog_3004 c
on b.userid=c.roleid and b.zoneid=c.zoneid
and  c.DateID=20180605
where a.DateID<c.DateID
group by a.DateID,a.platid,DATEDIFF(cast(c.DateID as datetime),cast(a.DateID as datetime))
order by a.DateID;