题解 | 最长连续登录天数

#20230101-20230131
#max
#lianxudenglu 


with t1 as (
    select *,row_number() over (partition by td.user_id order by td.fdate) as rn
from tb_dau td),
t2 as(select *, date_sub(t1.fdate,interval rn day) as if_continue
from t1),
t3 as(
    select user_id, if_continue,count(*) as days_consec
    from t2
    where fdate between "2023-01-01" and "2023-01-31"
    group by user_id,if_continue
)
select user_id,max(days_consec) as max_consec_days
from t3
group by user_id