题解 | 最长连续登录天数

select user_id
,max(连续登录天数) as max_consec_days
from 
(
    select 
        distinct user_id,
        max(日期排序)-min(日期排序)+1 as 连续登录天数
    from (
        select distinct 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 year(fdate)=2023 and month(fdate)=1
    ) as uniq_id_date
GROUP BY user_id,初始日期
) as id_last_next_date
group by user_id