mysql 连续签到查询

CREATE TABLE user_sign_in (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    sign_date DATE NOT NULL
);

WITH ranked_sign_ins AS (
    SELECT
        user_id,
        sign_date,
        DATEDIFF(sign_date, LAG(sign_date) OVER (PARTITION BY user_id ORDER BY sign_date)) AS days_diff
    FROM user_sign_in
),
sign_ins_with_group AS (
    SELECT
        user_id,
        sign_date,
        SUM(CASE WHEN days_diff = 1 THEN 0 ELSE 1 END) OVER (PARTITION BY user_id ORDER BY sign_date) AS group_id
    FROM ranked_sign_ins
)
SELECT
    user_id,
    COUNT(*) AS consecutive_days
FROM sign_ins_with_group
GROUP BY user_id, group_id
ORDER BY user_id, consecutive_days DESC;