题解 | 最长连续登录天数

-- 首先需要给登录日期排序
with temp as (
    select
        fdate,
        user_id,
        row_number() over(partition by user_id order by fdate asc) as rnk
        from (
            select
                distinct fdate, user_id
                from tb_dau
                WHERE fdate >= '2023-01-01' AND fdate <= '2023-01-31'
        ) t
),

temp1 as (
    select
        user_id,
        date_sub(fdate , interval rnk day) as grp_date,
        count(1) as total
        from temp
        group by user_id, DATE_SUB(fdate, INTERVAL rnk DAY)
)

select 
    user_id,
    max(total) as max_consec_days
    from temp1
    group by user_id