题解 | 最长连续登录天数

SELECT C.user_id,
       MAX(num) AS max_consec_days
FROM (
    SELECT B.user_id,
       COUNT(start_date) AS num
FROM  (
         SELECT A.user_id,
                date_sub(fdate,interval ranking day) AS start_date
         FROM (
                   SELECT user_id,
                          fdate,
                          row_number()over(partition by user_id order by fdate) as ranking 
                   FROM tb_dau AS TD 
                   WHERE fdate BETWEEN '2023-01-01' AND '2023-12-31'
              )  AS A 
     ) AS B
GROUP BY B.user_id,start_date
) AS C
GROUP BY user_id