select uid,date_format(t,'%Y%m') as month,sum(gc) as coin
from (select uid,
       t,
       case row_number() over (partition by date_sub(t, interval rk day),uid order by t) % 7
           when 0 then 7
           when 3 then 3
           else 1 end as gc
from (select distinct uid, date(in_time) as t, dense_rank() over (partition by uid order by date(in_time)) as rk
      from tb_user_log
      where date(in_time) between '2021-07-07' and '2021-10-31'
        and artical_id = 0
        and sign_in = 1) a) b
group by 1,2
order by 2,1;

# 另一个
WITH
daily_sign AS (
    -- 获取有效签到记录（artical_id=0且sign_in=1），并按用户和签到日期去重
    SELECT DISTINCT uid, DATE(in_time) AS sign_date
    FROM tb_user_log
    WHERE artical_id = 0 
      AND sign_in = 1 
      AND in_time >= '2021-07-07' 
      AND in_time < '2021-11-01' -- 活动截止到10月底
),
sign_group AS (
    -- 生成连续签到分组标记
    SELECT 
        uid, 
        sign_date,
        -- 判断是否为新连续组的开始（当前行与上一条签到日期间隔>1天）
        SUM(is_new_group) OVER (PARTITION BY uid ORDER BY sign_date) AS group_id
    FROM (
        SELECT 
            uid,
            sign_date,
            -- 当前日期与前一签到日期的间隔（首条记为1）
            CASE WHEN DATEDIFF(sign_date, LAG(sign_date, 1, '2000-01-01') OVER w) > 1 
                 THEN 1 ELSE 0 END AS is_new_group
        FROM daily_sign
        WINDOW w AS (PARTITION BY uid ORDER BY sign_date)
    ) t
),
sign_series AS (
    -- 计算组内连续天数
    SELECT 
        uid,
        sign_date,
        ROW_NUMBER() OVER (PARTITION BY uid, group_id ORDER BY sign_date) AS series_days
    FROM sign_group
),
coins_calc AS (
    -- 计算每日金币（基础+额外）
    SELECT 
        uid,
        sign_date,
        1 + 
          CASE 
            WHEN series_days % 7 = 3 THEN 2  -- 第3天额外+2（如第3、10、17天）
            WHEN series_days % 7 = 0 THEN 6  -- 第7天额外+6（如第7、14、21天）
            ELSE 0 
          END AS daily_coins
    FROM sign_series
)
-- 按月聚合金币总数
SELECT 
    uid,
    DATE_FORMAT(sign_date, '%Y%m') AS month,
    SUM(daily_coins) AS coin
FROM coins_calc
GROUP BY uid, DATE_FORMAT(sign_date, '%Y%m')
ORDER BY month, uid;