case when套case when｜#连续签到领金币#

WITH a AS (
SELECT uid, DATE(in_time) AS dt, ROW_NUMBER() OVER (PARTITION BY uid ORDER BY in_time) AS rn
FROM tb_user_log
WHERE artical_id = 0 AND sign_in = 1 AND DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31'
),

b AS (
SELECT uid, DATE_FORMAT(MIN(dt), "%Y%m" ) AS month, COUNT(*) AS cnt
FROM a
GROUP BY uid, DATE_SUB(dt, INTERVAL rn DAY)
ORDER BY uid, month
)

SELECT uid, month, SUM(CASE WHEN cnt <= 7 THEN
                            CASE WHEN cnt % 7 > 0 and cnt % 7 < 3 THEN cnt 
                                 WHEN cnt % 7 >= 3 and cnt % 7 < 7 THEN cnt+2 
                                 WHEN cnt % 7 = 0 THEN cnt+2+6 
                            END
                        ELSE
                            FLOOR(cnt / 7) * (7 + 2 + 6) +  
                            CASE WHEN cnt % 7 > 0 AND cnt % 7 < 3 THEN cnt % 7                                WHEN cnt % 7 >= 3 AND cnt % 7 < 7 THEN cnt % 7 + 2  
                                 WHEN cnt % 7 = 0 THEN 0  
                            END
                       END) AS coin
FROM b
GROUP BY uid, month
ORDER BY month, uid


# artical_id = 0 AND sign_in = 1
# DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31'
# COIN = 每日签到1金币+连续签到3、7天奖励金币
# GROUP BY uid
# ORDER BY month, uid