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

#取商一定要用向下取整floor(),否则会报错！
#时间连续问题，记得要去重
with a as (select 
distinct uid,date(in_time) as dt 
from tb_user_log
where date(in_time) between '2021-07-07' and '2021-10-31' and artical_id=0 and sign_in=1),
b as (
select 
uid,dt,
date_sub(dt,interval row_number() over (partition by uid order by dt) day) flag
from a),
c as (
select 
uid,flag,date_format(dt,'%Y%m') as month,count(*) as cnt
from b 
group by uid,flag,date_format(dt,'%Y%m')
),
d as (
select uid,month,
case when cnt<3 then cnt
    when cnt>=3 and cnt<7 then cnt+2
    when cnt=7 then 7+2+6
    when cnt>7 and cnt%7<3 then cnt%7+floor(cnt/7)*15
    when cnt>7 and cnt%7>=3 and cnt%7<7 then cnt%7+2+floor(cnt/7)*15
    end as coin
from c)

select uid,month,round(sum(coin)) as 'coin'
from d
group by uid,month
order by month,uid