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

select uid,month,sum(coin) as coin from
(select uid,month,rowsub,count(*) as cnt,
case when mod(count(*),7)>2 then floor((count(*)/7))*15+mod(count(*),7)+2 else floor((count(*)/7))*15+mod(count(*),7) end as coin
from
(select uid,month,rown,rowsub from
(select distinct uid,DATE_FORMAT(in_time,'%Y%m') month,
row_number() over(partition by uid order by date(in_time)) rown,
date(in_time)-row_number() over(partition by uid order by date(in_time)) rowsub
from tb_user_log
where sign_in=1 and artical_id=0 and date(in_time) between '2021-07-07' and '2021-10-31')c
)a
group by uid,month,rowsub
)b
group by uid,month
order by uid,month