题解 | 最长连续登录天数

思路：

1. 对登录记录按照天数去重，筛选时间
2. 计算日期排名差，后续依次进行分组
3. 分组后，最大值即为连续登录最多天的

with user_login as (
    select distinct user_id, fdate
    from tb_dau
    where fdate between '2023-01-01' and '2023-01-31'
)
, user_rank as (
    select user_id
           , fdate
           , row_number() over(partition by user_id order by fdate) as rn
           , date_sub(fdate, interval row_number() over(partition by user_id order by fdate) day) as grp_key
    from user_login
)
, consec_day as (
    select user_id
           , count(*) as consec_days
    from user_rank
    group by user_id, grp_key
)

select user_id, max(consec_days) as max_consec_days
from consec_day
group by user_id