题解 | 最长连续登录天数

select user_id,max(cnt) as max_consec_days
from (
select user_id,dategroup,count(1) as cnt
from (
select fdate,user_id,date_sub(fdate,interval rn day) as dategroup
from(
select fdate
,user_id
,row_number() over(partition by user_id order by fdate) as rn
from tb_dau
where fdate between '2023-01-01' and '2023-01-31'
) t
) tt
group by user_id,dategroup
) ttt
group by user_id

连续天数已经有肌肉记忆了，先排序，然后算出一个等差的日期组，再聚合就完了