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

SELECT user_id, consec_days AS max_consec_days
FROM (SELECT user_id, consec_days, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY consec_days DESC ) AS rn2
      FROM (SELECT user_id, COUNT(fdate - rn) AS consec_days
            FROM (SELECT *,
                         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY fdate) AS rn
                  FROM tb_dau
                  WHERE fdate >= '2023-01-01'
                    AND fdate <= '2023-01-31') t
            GROUP BY user_id, fdate - rn) t2) t3
WHERE rn2 = 1