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

select
    user_id,
    max(consec_days) as max_consec_days
from
    (
        select
            user_id,
            initial_day,
            count(1) as consec_days
        from
            (
                select
                    user_id,
                    date_sub(fdate, interval rk day) as initial_day
                from
                    (
                        select
                            user_id,
                            fdate,
                            row_number() over (
                                partition by
                                    user_id
                                order by
                                    fdate
                            ) as rk
                        from
                            tb_dau
                        where
                            date_format (fdate, '%Y%m%d') between 20230101 and 20230131
                    ) t1
            ) t2
        group by
            1,
            2
    ) t3
group by
    1