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

with zb_0 as (select *,CASE WHEN da >= 7 AND FLOOR(MOD(da,7) / 3) < 1 THEN FLOOR(da / 7) * 15 + MOD(da,7)  /*大于7天且余数不大于3*/
    WHEN da >= 7 AND FLOOR(MOD(da,7) / 3) >= 1 THEN FLOOR(da / 7) * 15 + 5 + MOD(da,7) - 3  /*大于7天且余数大于3*/
    WHEN da >= 3 THEN 5 + da - 3 /*大于三天*/
    ELSE da /*小于三天*/
END AS s
from 
(select uid,min(in_time) in_time,in_time-row_1 diff_time, /*求差,不同值个数就是各个连续签到天数*/
count(*) da
from 
(select uid,date(in_time) in_time,row_number() over(partition by uid order by in_time) row_1 
from tb_user_log
where artical_id=0 and sign_in=1 and date(in_time) BETWEEN '2021-07-07' and '2021-10-31') zb /*限定条件*/
group by uid,diff_time) zb1)
select uid,date_format(in_time,"%Y%m") month,sum(s) coin
from zb_0
group by uid,month
order by month,uid