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

with t1 as (select uid,date(in_time) dt
from tb_user_log 
where artical_id = 0
and in_time < '2021-11-01'
and in_time >= '2021-07-07'
and sign_in = 1
group by uid,date(in_time)
),
t2 as (select uid,dt,rank() over (partition by uid order by dt ASC) rank_num
from t1
),
t3 as (select uid,dt,rank_num,date_add(dt,interval - rank_num day) rank_dt
from t2)

select uid,replace(substr(dt,1,7),'-','') month,
sum(case when coin_num % 7 = 3 then 3 when coin_num % 7 = 0 then 7 else 1 end) coin
from (select uid,dt,rank() over (partition by uid,rank_dt order by dt) coin_num
from t3) u 
group by uid,replace(substr(dt,1,7),'-','')
order by month,uid ASC