题解 | #连续签到领金币#

SELECT uid, month,
SUM(CASE WHEN MOD(rnk_1, 7) = 3 THEN 3
     WHEN MOD(rnk_1, 7) = 0 THEN 7
     ELSE 1 END) AS coin
FROM(
    SELECT uid, month,
    RANK() OVER(PARTITION BY uid, DATE_SUB(dt, INTERVAL rnk DAY) ORDER BY dt) AS rnk_1
    FROM(
        SELECT DISTINCT uid,
        DATE_FORMAT(in_time, '%Y%m') AS month,
        DATE(in_time) AS dt,
        RANK() OVER(PARTITION BY uid ORDER BY in_time) AS rnk
        FROM tb_user_log
        WHERE DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31'
        AND sign_in = 1
        AND artical_id = 0
        ) AS tb1
    ) AS tb2
GROUP BY uid, month
ORDER BY month, uid
;