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

select uid,concat('2021',m) as "month", sum(coin_m) as "coin"
from (
select uid,count(1),m,case when count(1) >=7 then (floor(count(1)/7)*15 + (case when (count(1)%7)>=3 then count(1)%7+2 else count(1)%7 end))
                           when count(1) >=3 then count(1)+2 else count(1) end "coin_m"
from(

select uid,in_time,m,(day(in_time) - ranking) as "const_d",sign_in
from(

select uid,in_time,lpad(month(in_time),2,0) as "m",row_number()over(partition by uid order by in_time) as "ranking",sign_in
from tb_user_log
where in_time between '2021-07-07 00:00:00' and '2021-10-31 23:59:59' 
and artical_id = 0 and sign_in = 1
)t1   
)t2
group by uid,const_d,m    
)t3     
group by uid,m          
order by month,uid