题解 | 最长连续登录天数

with t1 as 
(
    select 
    user_id,
    row_number() over(partition by user_id order by fdate) as rk,
    date_sub(fdate, interval (row_number() over(partition by user_id order by fdate)) day) as ori_date
    from tb_dau
    where fdate between '2023-01-01' and '2023-12-31'
)
,
t2 as 
(
select 
user_id,
count(user_id) as consec_days
from t1
group by user_id,ori_date
)

select 
user_id,
max(consec_days) as max_consec_days
from
t2
group by 
user_id