题解 | 最长连续登录天数

with t as (select rank() over(partition by user_id order by fdate) as rk,fdate,user_id
from tb_dau
where year(fdate) = 2023 and month(fdate) = 1
),tmp as(
select date_sub(fdate,interval rk day) as flag,user_id
from t ),ttt as
(select user_id,count(*) over(partition by flag,user_id) as ee
from tmp
),a as(
select rank() over(partition by user_id order by ee desc) as rkk,ee,user_id
from ttt)
select user_id,ee as max_consec_days
from a
where rkk = 1
group by user_id

纯暴力 fdate-rank相等即为连续