连续登陆sql实现

背景

已知：有个用户登陆表，包含用户id和用户登陆时间，表名users

求：用户连续登陆天数，和用户最大连续登陆天数

思路：先把每个用户按照登陆时间排序rn，在用登陆时间减rn得到一个值，如果值一样，说明是一段的连续登陆，然后把不连续的天数去掉（上一步得到的值只有一个，说明是不连续的），然后得出每段连续的登陆

求连续登陆天数：

select userid,sum(sc) as sumdays from 
(  
        select userid,date_sub,count(*)  as sc from
        (
        select userid,logindate-rn as date_sub from 
            (
select  userid,logindate,row_number() over (partition by userid order by                logindate)  as rn from  users
            )a
        )b   
        group by  userid, date_sub having count(*)>1
)d  group by userid

求最大连续登陆天数

select userid,max(sc) as maxdays from 
(  
        select userid,date_sub,count(*)  as sc from
        (
        select userid,logindate-rn as date_sub from 
            (
select  userid,logindate,row_number() over (partition by userid order by                logindate)  as rn from  users
            )a
        )b   
        group by  userid, date_sub having count(*)>1
)d  group by userid