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

select
    user_id,
    max(连续登录天数) as max_consec_days
from
(select
    user_id,
    max(日期排序)-min(日期排序)+1 as 连续登录天数
from
(select 
    user_id,
    fdate,
    row_number() over(partition by user_id order by fdate) as 日期排序,
    date_sub(fdate, interval row_number() over(partition by user_id order by fdate) day) as 初始日期
from tb_dau
where fdate between '2023-01-01' and '2023-01-31') as t1
group by user_id, 初始日期) as t2
group by user_id