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

select uid,
       date_format(dt,'%Y%m') month,
       sum(case when rn=3 then 3
                when rn=0 then 7
           else 1 end
        ) as coin
from (
    select uid,dt,
       row_number()over(partition by uid,sub_dt order by dt)%7 rn
    from
    (
        select uid,dt,
            date_sub(dt,interval row_number()over(partition by uid order by dt) day) sub_dt
        from  
        (
            select uid,date(in_time) dt
            from tb_user_log
            where sign_in = 1
            and artical_id = 0
            and date(in_time) between '2021-07-07' and '2021-10-31'
            group by uid,date(in_time)
        )t1
    )t2
)t3
group by uid,date_format(dt,'%Y%m')
order by 2,1