题解 | 连续签到领金币

with t1 as (
    select
        uid,
        date(in_time) as dt,
        date_format(in_time,'%Y%m') as mon,
        -- 生成连续的排名，用于判断连续签到
        row_number() over(partition by uid order by date(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'  -- 活动期间
),

t2 as (
    select
        uid,
        dt,
        mon,
        -- 用日期减去排名得到分组标识，相同分组的日期是连续的
        date_sub(dt, interval rn day) as grp
    from t1
),

t3 as (
    -- 展开连续签到，按天计算每天获得的金币
    select 
        uid,
        mon,
        dt,
        -- 计算在这一轮连续签到中是第几天
        row_number() over(partition by uid, grp order by dt) as day_in_grp
    from t2
),

t4 as (
    select 
        uid,
        mon,
        dt,
        -- 根据第几天计算当天获得的金币
        case 
            when day_in_grp % 7 = 3 then 3  -- 第3天：基础1+额外2
            when day_in_grp % 7 = 0 then 7  -- 第7天：基础1+额外6
            else 1                          -- 其他天数：基础1
        end as daily_coin
    from t3
)

select 
    uid,
    mon,
    sum(daily_coin) as coin
from t4
group by uid, mon
order by uid, mon;