题解 | 最长连续登录天数

with first as (
select
   distinct user_id,fdate,row_number() over (partition by user_id order by fdate asc) as rn
from tb_dau
where fdate between '2023-01-01' and '2023-01-31'

),
days as (select user_id,
                fdate,
                rn,
                date_sub(fdate, interval rn day)     as day
         from first)
        
,
day_cnt as (
select user_id,
    day,
      count(day) cnt
from days
group by user_id,day
)
select user_id,max(cnt)  as max_consec_days
from day_cnt
group by user_id
;