题解 | 最长连续登录天数

SELECT user_id,max(consec_days) AS max_consec_days
from
(
SELECT user_id,date_login_processed,count(date_login_processed) AS consec_days
from
(
SELECT
    user_id,ranking,
    DATE_SUB(fdate, interval ranking day) as date_login_processed
FROM
    (
        SELECT
            *,
            rank() over (
                partition by
                    user_id
                order by
                    fdate
            ) as ranking
        from
            tb_dau
    ) AS A
) AS B
GROUP BY user_id,date_login_processed
) AS A
GROUP BY user_id