题解 | #连续签到领金币#

with t1
as(
    select
    uid
    ,date(in_time) dt
    from tb_user_log
    where artical_id = 0
    and date(in_time) between '2021-07-07' and '2021-10-31'
    and sign_in = 1
),

t2 as
(
    select
    uid
    ,dt
    ,row_number()over(partition by uid order by dt) rk
    from t1
)

select
uid
,date_format(dt, '%Y%m') month
,sum(coin) coin
from
(
    select
    uid
    ,dt
    ,case when con_sign = 3 then 3
        when con_sign = 0 then 7
    else 1 end as coin
    from(
        select
        uid
        ,dt
        ,date_sub(dt, interval rk day) dt_tmp
        ,row_number()over(partition by uid, date_sub(dt, interval rk day) order by dt) % 7 con_sign
        from t2
    ) a
) b
group by uid, month
order by month, uid