题解 | 连续签到领金币

with t0 as(
select uid,date_format(in_time,'%Y%m%d') as dt ,DATE_FORMAT(in_time,'%Y%m') as month,sign_in from tb_user_log where artical_id=0 and in_time between '2021-07-07' and '2021-11-01' and sign_in!=0
),
t1 as (
select uid,month,count(cnt) as cn from (
select uid,month,dt-rk+sign_in as cnt from(
select uid,month,dt,row_number() over(partition by uid order by dt) as rk ,sign_in from t0 )t) tmp group by 1,2,cnt)

select uid,month,sum(case when cn>=3 and cn<7 then cn+2 when cn>=7 then floor(cn/7)*15+floor(cn%7/3)*5+cn-7*floor(cn/7)-3*floor(cn%7/3) else cn end ) as coin from t1 group by 1,2 order by 2,1