题解 | 连续签到领金币

with t1 as(
    select 
        uid,
        date(in_time) dt,
        row_number() over(partition by uid,month(date(in_time)) order by date(in_time) asc) rk
    from
        tb_user_log
    where artical_id = 0 and sign_in = 1 and date(in_time) >= '2021-07-07' and
    date(in_time) < '2021-11-01'
)
,t2 as(
    select
        uid,
        date_format(dt,'%Y%m') month,
        case when count(1)%7 <3  then count(1) div 7 *(6+7+2) + count(1)%7
            when count(1)%7 >= 3  then count(1) div 7 *(6+7+2) + count(1)%7+2
            when count(1)%7 = 0 and count(1) div 7 = 0 then 0 
            end coin_temp

    from
        t1
    group by 1,2,date_sub(dt,interval rk day)

)
select 
    uid,
    month,
    sum(coin_temp) coin
from
    t2
group by 1,2

so easy