题解 | 最长连续登录天数

#筛选每个用户在范围内的日期并对进行排序，用row_number(不用dense_rank否则会多算)
#用日期减去排序的序号
#统计减去后的结果相同的个数
#每个用户结果相同个数即为连续登录天数，最后用max即可求出最大连续登录天数
with daterank as (
    select user_id,
    fdate,
    row_number()over(partition by user_id order by fdate) as ranking
    from tb_dau
    where fdate between '2023-01-01' and '2023-01-31'
),
datecontrol as (
    select user_id,
    fdate,
    date_sub(fdate,interval ranking day) as conranking
    from daterank
),
rc as(
    select user_id,
    count(conranking) as cnum
    from datecontrol
    group by user_id,conranking
)
select user_id,
max(cnum) as max_consec_days
from rc
group by user_id;