题解 | 最长连续登录天数

SELECT 
	DISTINCT user_id,
	max(max_consec_days) as max_consec_days
FROM 
(
	SELECT user_id,
		count(date) as max_consec_days
	FROM
	(	
		SELECT *,
			DATE_SUB(fdate,INTERVAL date_order day) as date
		from
		(
			SELECT 
				DISTINCT *,
				ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY fdate) AS date_order
			FROM tb_dau
			where 
				fdate between '2023-01-01' and '2023-01-31'
		) as t1
	) as t2
	GROUP BY user_id,date 
) as t3 
GROUP BY user_id