题解 | 最长连续登录天数

WITH rk_tb as (
SELECT distinct fdate 
    ,user_id
    ,row_number()over(partition by user_id order by fdate) rk
FROM tb_dau
WHERE date_format(fdate,'%Y%m') = '202301'    
),
cont_tb as(
SELECT user_id
    ,count(user_id) cont_day
FROM rk_tb
GROUP BY user_id,date_sub(fdate,interval rk day)
)
SELECT user_id
    ,max(cont_day) max_consec_days
FROM cont_tb
GROUP BY user_id