题解 | 连续签到领金币

select uid,month,sum(coin) as coin
from
(SELECT 
    uid,
    DATE_FORMAT(dt, '%Y%m') AS month,
    CASE
            DENSE_RANK() over(PARTITION BY DATE_SUB(dt,INTERVAL rk day),uid ORDER BY dt )%7 
            WHEN 3 THEN 3
            WHEN  0 THEN 7
            ELSE 1
        END AS coin
FROM 
    (
        SELECT 
            uid,
            DATE_SUB(dt, INTERVAL (rk -1) DAY) AS flag,  
            dt,
            rk
        FROM 
            (
                SELECT DISTINCT 
                    uid, 
                    DATE(in_time) AS dt,
                    dense_rank() OVER (PARTITION BY uid ORDER BY DATE(in_time)) AS rk
                FROM tb_user_log
                WHERE DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31'
                  AND artical_id = 0
                  AND sign_in = 1
            ) AS a
    ) AS b)c
GROUP BY 
    uid, month
ORDER BY 
    month, uid;

防不甚防啊，排完第一次序只是选出连续登录的flag，后面算coin，要基于flag再排一个，这时的才是正确rank