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

with A as (
select distinct uid,date(in_time)date from tb_user_log
where date(in_time) between '2021-07-07' and '2021-10-31'
and sign_in = 1
and artical_id = 0
)
# 排序
,B as (
select uid
,date
,date - row_number()over(partition by uid order by date) d
,row_number()over(partition by uid order by date)rn
from A
)
# 总签到次数
,C as (
select uid,date_format(date,'%Y%m')month,count(*)coin
from B
group by 1,2
)
# 再排一次
,D as (
select uid
,d
,row_number()over(partition by uid,d order by d)rn
from B
)
# 分组取连续登录次数大于2的
,E as (
select uid,d,max(rn)rn from D group by 1,2 having rn>2
)
# 对上表结果细分 得额外可领金币数
,F as (
select uid,substr(d,1,6)month
,case when max(rn) between 3 and 6 then 2
when max(rn)=7 then 8
when max(rn)>7 and max(rn)%7<3 then floor(max(rn)/7)*8
when max(rn)>7 and max(rn)%7>2 then floor(max(rn)/7)*8 + 2
end coin
from E
group by 1,2
)
# 连表
select C.uid,C.month,C.coin + ifnull(F.coin,0) as coin from C
left join F using(uid)