题解 | 最长连续登录天数

WITH t0 AS ( SELECT fdate, row_number() over ( PARTITION BY user_id ORDER BY fdate ) AS dt, user_id FROM tb_dau GROUP BY 1, 3 ),
t1 AS ( SELECT fdate, DAY ( fdate )- dt AS cnt, user_id FROM t0 ) 
SELECT
user_id,
num AS max_consec_days 
FROM
	(
	SELECT
		user_id,
		count( cnt ) AS num,
		row_number() over ( PARTITION BY user_id ORDER BY count( cnt ) DESC ) AS rk 
	FROM
		t1 
	GROUP BY
		1,
		cnt 
	) t 
WHERE
	rk =1