题解 | 最长连续登录天数

with t1 as(
select 
    user_id,
    fdate 
from tb_dau where year(fdate) = 2023 and month(fdate) = 1),

t2 as(
select 
    t1.user_id,
    t1.fdate,
    row_number() over(partition by t1.user_id order by t1.fdate) date_rank
from t1),

t3 as(
select  
    # *,
    # date_sub(t2.fdate,interval t2.date_rank day) forward_date
    t2.user_id,
    count(1) consec_days
from t2 
group by t2.user_id,date_sub(t2.fdate,interval t2.date_rank day))


select 
    t3.user_id,
    max(t3.consec_days) max_consec_days
from t3 
group by t3.user_id