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

# 只有artical_id为0时sign_in值才有效
SELECT e.uid, e.month, SUM(e.coins) AS coin
FROM(
    SELECT d.uid, DATE_FORMAT(d.sign_date,"%Y%m") AS month,
        CASE WHEN MOD(d.sign_rk,7) = 3 THEN 3
             WHEN MOD(d.sign_rk,7) = 0 THEN 7
             ELSE 1 
        END AS coins
    FROM(
        SELECT c.uid, c.sign_date, c.sign_date_rk,
            ROW_NUMBER() OVER (PARTITION BY c.uid, c.diff ORDER BY c.sign_date) AS sign_rk
        FROM(    
            SELECT b.uid, b.sign_date, b.sign_date_rk,
                DATE_SUB(b.sign_date, INTERVAL b.sign_date_rk DAY ) AS diff
            FROM (
                SELECT a.uid, a.sign_date,
                    ROW_NUMBER() OVER (PARTITION BY a.uid ORDER BY a.sign_date) AS sign_date_rk
                FROM(
                    SELECT DISTINCT uid, DATE_FORMAT(in_time, "%Y%m%d" ) AS sign_date
                    FROM tb_user_log
                    WHERE artical_id = 0 
                    AND sign_in = 1 
                    AND DATE_FORMAT(in_time, "%Y%m%d" ) BETWEEN '20210707' AND '20211031'
                    GROUP BY uid, sign_date
                    ) AS a
                ) AS b
            ) AS c 
        ) AS d
    ) AS e
GROUP BY e.uid, month
ORDER BY month, e.uid
;