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

这题可谓是非常高质量了！！ 大家如果刚开始不会可以先去做SQL29 里面有连续天数的计算方法！！！！（强烈推荐） 1.我们只要先排序，再将日期减去排序值就可以得到第一天连续打卡的时间，从而解决打断的问题。tmp表是得到排序数，base表是减去排序值。 2.之后我们根据 用户 和 每个阶段连续打卡的第一天 进行分组就可以得到今天是连续打卡的第几天 3.之后根据数学规则mod7就可以根据规则得到改天得到的金币 4.再根据月进行聚合求sum即可

select uid,date_format(sign_dt,"%Y%m") as mon,sum(coin)
from
(select uid,TIMESTAMPADD(day,-diff+1,sign_dt) as start_dt,
       case (DENSE_RANK() over(partition by uid ,TIMESTAMPADD(day,-diff+1,sign_dt)
                        order by sign_dt))%7  when 3 then 3
                                                 when 0 then 7
                                                 else 1 end as coin,
      sign_dt
from
(select uid,DATE_FORMAT(in_time,"%Y%m%d") as sign_dt,
       DENSE_RANK() over(partition by uid order by DATE_FORMAT(in_time,"%Y%m%d"))
       as diff
from tb_user_log
where DATE_FORMAT(in_time,"%Y%m%d") between "20210707" and "20211031"
      and artical_id=0 and sign_in=1) tmp) base
group by uid,date_format(sign_dt,"%Y%m")
order by mon,uid