题解 | 最长连续登录天数

with dis_data as (
    -- 第一步：去重 + 过滤时间范围（避免同一天多次登录干扰）
    select distinct user_id,
    fdate
    from tb_dau
    where fdate between '2023-01-01' and '2023-01-31'
),
row_data as (
    -- 第二步：按用户+日期排序，生成组内唯一行号
    select user_id,
    fdate,
    row_number () over ( partition by user_id order by fdate) as rn
    from dis_data
),
group_data as (
    -- 第三步：计算差值，连续登录日期差值相同
    select  user_id,
    fdate,
    date_sub(fdate,interval rn day) as group_flag
    from row_data
),
consec_data as (
    -- 第四步：按用户+分组标识统计连续天数
    select user_id,
    count(1) as consec_days
    from group_data
    group by user_id,group_flag)
	-- 第五步：取每个用户的最大连续天数
select user_id,
max(consec_days) as max_consec_days
from consec_data
group by user_id