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

from(select uid,dt,(1+#基础积分+符合条件的额外加分
       case
            when ar=3 and sign_in=1 then 2
            when ar=7 and sign_in=1 then 6
            when sign_in=0 then -1#排除没签到还被计一分的情况
            else 0
            end) as arr
from(select  uid,dt,sign_in,ar,dense_rank() over (partition by uid,day(dt)-ar+1 order by dt)ar1#以每组签到周期的第一天为分组依据，解决断签重算的问题
from(select uid,date(in_time)dt,sign_in,(dense_rank() over (partition by uid,sign_in order by date(in_time))%7+#这步解决7日一循环的排序
            case when dense_rank() over (partition by uid,sign_in order by date(in_time))%7=0 then 7 else 0#这步将第7天的排序值0变为7
                end) as ar
       from tb_user_log)b1)b2)b3
group by uid,d