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

SELECT 
uid,
month,
SUM(coin) AS coin

FROM(
    SELECT 
    *,
    DATE_FORMAT(in_time, '%Y%m') AS month,
    CASE WHEN DENSE_RANK() OVER(PARTITION BY uid, DATE_SUB(DATE(in_time), INTERVAL ori_rk DAY) ORDER BY DATE(in_time)) % 7 = 0 THEN 7
         WHEN DENSE_RANK() OVER(PARTITION BY uid, DATE_SUB(DATE(in_time), INTERVAL ori_rk DAY) ORDER BY DATE(in_time)) % 7 = 3 THEN 3
         ELSE 1
	END AS coin
    
    
    FROM(
        SELECT
        *,
        DATE(in_time) AS dt,
        DENSE_RANK() OVER(PARTITION BY uid ORDER BY DATE(in_time)) AS ori_rk
        
        FROM tb_user_log
        
        WHERE 
        artical_id = '0'
        AND sign_in = '1' 
        AND DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31') AS t1) AS t2
        
GROUP BY uid, month

ORDER BY month, uid