题解 | 连续签到领金币

-- 创建临时表
WITH a AS(
    SELECT uid, artical_id, DATE(in_time) dt, sign_in
    FROM tb_user_log
),
b AS(
    SELECT uid, artical_id, dt, sign_in
    FROM a
    WHERE artical_id = 0 AND DATE(dt) BETWEEN '2021-07-07' AND '2021-10-31' AND sign_in = 1
),
c AS (
    SELECT 
        uid,
        dt,
        ROW_NUMBER() OVER(PARTITION BY uid ORDER BY dt) rk
    FROM b
),
d AS(
    SELECT 
    uid,
    dt,
    DATE_SUB(dt,INTERVAL rk DAY) dt_tmp,
    ROW_NUMBER() OVER(PARTITION BY uid, DATE_SUB(dt,INTERVAL rk DAY)  ORDER BY dt) % 7 con_sign
    FROM c
),
e AS(
    SELECT
    uid,
    dt,
    CASE WHEN con_sign = 3 THEN 3
        WHEN con_sign = 0 THEN 7
        ELSE 1 END coin
    FROM d
)
SELECT 
    uid,
    DATE_FORMAT(dt,'%Y%m') month,
    SUM(coin) coin
FROM e
GROUP BY uid, month
ORDER BY month, uid