题解 | 连续签到领金币

with
t0 as(
    select
        uid,
        concat(left(date,4),substr(date,6,2)) as month,
        case (dense_rank() over(partition by uid,dt_rn order by date))%7
            when 3 then 3
            when 0 then 7
            else 1 end as coin
    from(
        select 
            uid,
            left(in_time,10) as date,
            date_sub(left(in_time,10),interval row_number() over(partition by uid order by left(in_time,10)) day) as dt_rn
        from tb_user_log
        where artical_id=0 and sign_in=1 and (in_time between '2021-07-07 00:00:00' and '2021-11-01 00:00:00')
        ) a)
select
    uid,
    month,
    sum(coin)
from t0
group by uid,month
order by month,uid