题解 | 最长连续登录天数

select user_id,max(consec_days) as max_consec_days
from(
select user_id, new_date,count(1) as consec_days
from(
select user_id,date_sub(fdate,interval rn day) new_date
from(
select user_id,fdate,row_number() over(partition by user_id order by fdate asc) as rn
from tb_dau
where year(fdate)=2023 and month(fdate)=1
group by user_id,fdate
order by user_id,fdate) t) a
group by user_id,new_date) c
group by user_id