题解 | 连续签到领金币

select uid,month
,cast(sum(case when cnt%7=0 then cnt+8*cnt/7
when cnt%7!=0 and cnt%7<3 then  cnt+floor(cnt/7)*8
when cnt%7!=0 and cnt%7>=3 then  cnt+floor(cnt/7)*8+2
end) as signed) coin
from(
select uid,month,con
,count(*) cnt
from(
select uid,date_format(in_time,'%Y%m') month
,date(in_time)-row_number()over(partition by uid,date_format(in_time,'%Y%m') order by date(in_time)) con
# ,max(date_format(in_time,'%Y%m'))over(partition by uid) month
from tb_user_log
where artical_id=0
and sign_in=1
and date(in_time)>='2021-07-07'
and date_format(in_time,'%Y-%m')<'2021-11')a 
group by uid,month,con)b
group by uid,month