题解 | #最长连续登录天数#

with a as(select user_id,fdate,
date_sub(fdate, interval row_number() over (partition by user_id order by fdate) day) as diff
from tb_dau
where fdate between '2023-01-01' and '2023-01-31'),
b as (select user_id,count(diff) as login_days
from a group by user_id,diff)
select user_id,max(login_days) max_consec_days 
from b group by user_id