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

select uid,date_format(t2.dt,'%Y%m') as `month`,sum(t2.coin) as coin
from
(
select *,row_number() over (PARTITION BY t1.uid,t1.dt2 order by t1.dt) as rk2,
case when row_number()over(PARTITION BY t1.uid,t1.dt2 order by t1.dt)%7=3 then 3
when row_number()over(PARTITION BY t1.uid,t1.dt2 order by t1.dt)%7=0 then 7
else 1
end
as coin
from
(select distinct uid,date(in_time) as dt,sign_in,
row_number() over (partition by uid order by date(in_time)) as rk,
date(in_time) - (row_number() over (partition by uid order by date(in_time))) as dt2
from tb_user_log
where date(in_time) between '2021-07-07' and '2021-10-31'
and artical_id = 0
and sign_in = 1) as t1) as t2
group by date_format(t2.dt,'%Y%m'),uid
order by date_format(t2.dt,'%Y%m'),uid

首先筛选出去重的id和签到的有效时间，并且使用窗口函数进行排序，连续签到的日子的dt2应该是一样的，dt2不一样就说明连续签到的日期断了，然后按照uid和dt2再次聚合，得到连续签到不同天数下的coin数，使用case when函数取余得到奖励的金币，最后累加求和即可，把初步的限制条件放在第一个表里，然后逐步去计算