题解 | 最长连续登录天数

with temp0 as (
    select user_id,fdate,row_number()over(partition by user_id order by fdate asc) as rk
    from tb_dau td
    where fdate >= "2023-01-01" and fdate <= "2023-01-31"
), temp1 as (
    select user_id,adddate(fdate,interval -rk day) as new_date
    from temp0
), temp2 as (
    select user_id,count(*) as max_consec_days
    from temp1
    group by user_id,new_date
)

select user_id,max(max_consec_days) as max_consec_days
from temp2
group by user_id
order by user_id asc;