题解 | 最长连续登录天数

#20230101-20230131
#max
#lianxudenglu 

with trn as (
    select fdate, user_id, row_number() over(partition by user_id order by fdate asc) as rn
    from tb_dau

),
tdiff as (
    select trn.*,date_sub(trn.fdate, interval trn.rn day) as initial
    from trn
	where trn.fdate between '2023-01-01' and '2023-01-31'
),
tctn as (
    select tdiff.user_id,count(*) as days
    from tdiff
    group by tdiff.user_id,tdiff.initial
)
select tctn.user_id,max(days) as max_consec_days
from tctn
group by tctn.user_id