题解 | 最长连续登录天数

with t1 as
(select *,row_number() over (partition by user_id order by tb_dau.user_id,fdate)  ranking
 from tb_dau where  2023-01-01 <= fdate <= 2023-01-31),
t2 as (select *,date_sub(fdate,interval ranking day) ds from t1 ),
t3 as
(select t2.user_id,count(ds) max_consec_days from t2 group by t2.user_id,ds  ),
t4 as
(select *,row_number() over (partition by user_id order by max_consec_days desc ) ranking from t3)
select user_id, max_consec_days from t4 where ranking = 1;