题解 | 最长连续登录天数

-- 步骤1: 过滤日期并去重

WITH filtered_data AS (

SELECT DISTINCT

user_id,

fdate

FROM tb_dau

WHERE fdate BETWEEN '2023-01-01' AND '2023-01-31'

),

-- 步骤2: 生成连续登录分组标识

with_group_flag AS (

SELECT

user_id,

fdate,

-- 核心逻辑：日期 - 行号 = 固定值（用于标记连续区间）

fdate - ROW_NUMBER() OVER (

PARTITION BY user_id

ORDER BY fdate

) AS grp

FROM filtered_data

),

-- 步骤3: 按分组统计连续天数

consecutive_groups AS (

SELECT

user_id,

grp,

COUNT(*) AS consecutive_days

FROM with_group_flag

GROUP BY user_id, grp

)

-- 步骤4: 取每个用户的最大连续天数

SELECT

user_id,

MAX(consecutive_days) AS max_consec_days

FROM consecutive_groups

GROUP BY user_id;