题解 | 最长连续登录天数 | 用的是评论区的解题思路

-- 去重处理：确保每个用户每天的登录记录唯一。
with td as(
    select distinct user_id, fdate
    from tb_dau
    where fdate between '2023-01-01' and '2023-01-31'
),
-- 生成序号：为每个用户的登录日期按顺序编号。
td0 as (
    select 
        user_id,
        fdate,
        rank() over (partition by user_id order by fdate) as rn
    from td
),
-- 计算连续组标识：用登录日期减去序号，相同结果的日期属于同一连续组。
td1 as (
    select 
    user_id,
    date_sub(fdate,interval rn day) as new_date
    from td0
),
-- 统计每组连续天数：按用户和连续组分组，计算每组的连续天数。
td2 as(
    select user_id,
        new_date,
        count(*) as max_consec_days
    from td1
    group by user_id,
        new_date
)
-- 取最大值：对每个用户，取所有连续组中的最大天数。
select 
    user_id,
    max(max_consec_days) as max_consec_days
from td2
group by user_id
order by user_id;