题解 | #最长连续登录天数#

select c.user_id,max(c.cnt) as max_consec_days   
from
    (select b.user_id,count(*) as cnt
    from
        (select a.user_id,a.fdate,day(a.fdate)-ranking as cha
        from 
            (select user_id,fdate,
            row_number() over(partition by user_id order by fdate) as ranking 
            from tb_dau where year(fdate)=2023 and month(fdate) = 1) 
            as a )as b
    group by b.user_id,b.cha) as c
group by c.user_id

子表a：对每一个user_id的fdate进行排序标号

子表b：用日期中的日减去标号，如果是连续的日期，标号是一样的

子表c：用row_number对一样标号的序列进行重新标号，最大的值就是最大连续的天数