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

with a1 as (
    select distinct date(in_time) as in_time, uid, date_format(in_time, '%Y%m') as month, rank()over(partition by uid, date_format(in_time, '%Y%m') order by in_time) as rk
    from tb_user_log
    where in_time >= '2021-07-07' and date(in_time)<='2021-10-31' and artical_id = 0 and sign_in = 1
),
a2 as (
    select uid, date_sub(date(in_time), interval rk day) as continousday, month, in_time
    from a1
),
a3 as (
    select uid, month, rank()over(partition by uid, continousday order by in_time) as rn
    from a2 
),
a4 as (
    select uid, month, sum(case when rn%7= 3 then 3
                when rn%7= 0 then 7
                else 1 end) as coin
    from a3
    group by uid,month
)

select uid, month, coin
from a4
order by month, uid