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

SELECT uid, 
    DATE_FORMAT(DATE_ADD(new_dt, INTERVAL 1 DAY), '%Y%m') AS month,
    SUM(day_coin) AS coin
FROM (
    -- 获取各uid每个消费日期与其日期排名的差值，差值一样的消费日期即为连续签到的日期,
    -- 对差值和uid重新用窗口函数进行分组排序，计算每天获得的金币数（连续签到三天、七天时，第三天获得2+1=3个金币，第七天获得6+1=7个金币）
    SELECT uid, dt, dt_rank,
        DATE_SUB(dt, INTERVAL dt_rank DAY) AS new_dt,
        CASE DENSE_RANK() OVER(PARTITION BY DATE_SUB(dt, INTERVAL dt_rank DAY), uid ORDER BY dt) % 7
            WHEN 3 THEN 3
            WHEN 0 THEN 7
            ELSE 1
        END AS day_coin
    FROM (
        -- 使用窗口函数，对各uid有签到的日期进行排序
        SELECT uid, 
            DATE(in_time) AS dt,
            DENSE_RANK() OVER(PARTITION BY uid ORDER BY DATE(in_time)) AS dt_rank
        FROM tb_user_log
        WHERE DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31'
            AND artical_id = 0 AND sign_in = 1
    )rank_t
)sub_dt_t
GROUP BY uid, month
ORDER BY month, uid