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

select uid,month,sum(c) coin
from(
select uid,month,(case when count(sign)=7 then 15 when count(sign)>=3 then count(sign)+2 else count(sign) end) c
from(
select uid,date_format(in_time,'%Y%m') month,date_sub(date(in_time),
    interval (case when row_number()over(partition by uid order by in_time)%7=0 then 7 else row_number()over(partition by uid order by in_time)%7 end) day) sign
from tb_user_log
where sign_in=1 and date(in_time) between '2021-7-7' and '2021-10-31' and artical_id=0
)a
group by uid,month,sign
    )b