统计一个月内连续签到7天的用户

Question

如何仅用sql统计一个月内连续签到7天的用户呢？
数据库是mysql！

Answer 1

SELECT user_id
FROM (
    SELECT user_id, sign_date,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY sign_date) AS row_num,
           DATEDIFF(sign_date, 
                    LAG(sign_date) OVER (PARTITION BY user_id ORDER BY sign_date)
                   ) AS diff
    FROM sign_records
    WHERE sign_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)
) AS subquery
WHERE row_num >= 7 AND diff = 1
GROUP BY user_id
HAVING COUNT(*) = 7;