题解 | #最长连续登录天数#

-- 真实的情况下，应该考虑到一个人会出现同一天多次登录的情况
with  order_date as(
	select  distinct  user_id,
					date_format(fdate,'%Y-%m-%d') fdate,
					row_number() over(partition by user_id order by fdate )rn					 
	from  tb_dau
	where  left(fdate,7) = '2023-01'  
)

select user_id ,max(cn) as max_consec_days
from (
	select  user_id,count(diff_date)  as cn
	from(
		select user_id,
					fdate,
					date_sub(fdate,interval rn day) as diff_date
		from order_date
	)t1
	group by  user_id,diff_date
)t2
group by user_id