题解 | 最长连续登录天数

# 计算每个用户最大连续登录天数
select user_id,max(cn) max_consec_days
from (
    # 计算每个用户连续登录天数
    select user_id,s_day,count(1) cn
    from (
        # 计算每个用户开始连续登录的时间
        select date_sub(fdate,interval row_number()over(partition by user_id order by fdate) day) s_day
                ,user_id 
        from tb_dau
    ) tb1
    group by user_id,s_day
) tb2
group by user_id;