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

SELECT 
	user_id,
    MAX(连续登录天数) AS max_consec_days
FROM
	-- 第二张子表：根据初始起始天数进行分组,分别计算该月内的连续登录时间
	(SELECT 
		user_id,
		MAX(日期排序) - MIN(日期排序) + 1 AS 连续登录天数
	FROM
	 -- 第一张子表：若两行登陆日期-序号相同则证明这两行是连续的两天,并且能得到两行的相同的起始天数
		(SELECT user_id,
			fdate,
			ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY fdate) AS 日期排序,
			DATE_SUB(fdate, INTERVAL ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY fdate) DAY) AS 初始日期
		FROM tb_dau
		WHERE fdate BETWEEN '2023-01-01' AND '2023-01-31') AS t1
	GROUP BY user_id, 初始日期) AS t2
GROUP BY user_id;