题解 | 最长连续登录天数 - 刷一下这道面试高频题

with t1 as (
    select distinct
        *
    from
        tb_dau
    order by
        user_id,
        fdate
),
t2 as (
    select
        user_id,
        fdate,
        row_number() over (partition by user_id order by fdate) as rn
    from
        t1
),
t3 as (
    select
        user_id,
        date_sub(fdate, interval rn day) as a
    from
        t2
),
t4 as (
    select
        user_id,
        count(*) as cdate
    from
        t3
    group by
        user_id,
        a
)
select
    user_id,
    max(cdate) as max_consec_days
from
    t4
group by
    user_id

感觉写繁琐了