MySQL过程式编程，case when嵌套，差分(自联结完成)处理连续签到问题

题目地址

https://www.nowcoder.com/practice/aef5adcef574468c82659e8911bb297f

代码

# 还是过程式编程吧，否则万一签到奖励规则变了，SQL代码你根本不知道怎么改

# Keep in mind that MySQL user variables are deprecated since MySQL 8.0.13, so using window functions might be a better approach for future-proofing your query.



# 活动规则：（设计得很好，下次不要设计了）
# 从2021年7月7日0点开始，用户每天签到可以领1金币，并可以开始累积签到天数，连续签到的第3、7天分别可额外领2、6金币。
# 每连续签到7天后重新累积签到天数（即重置签到天数：连续第8天签到时记为新的一轮签到的第一天，领1金币）

with t1 as(
    select *,date(date_format(in_time,"%Y-%m-%d")) as signin_date from tb_user_log where sign_in=1 and artical_id=0 and in_time >= date("2021-07-07 00:00:00") and  in_time<date("2021-11-01 00:00:00")
)
,
signin_dates AS (
    SELECT
        signin_date,
        uid,
        
        # ----只需要改动这里的逻辑，其他不要动----------
        IF(signin_date = @prev_date + INTERVAL 1 DAY AND uid = @prev_uid 
        AND @consec_days <> 7
        , @consec_days := @consec_days + 1, @consec_days := 1) AS consec_days,

        (case when uid = @prev_uid then
                (case when @consec_days=3 then @coin := @coin + 1+2
                      when @consec_days=7 then @coin := @coin + 1+6
                      else @coin := @coin + 1 
                end)
              else 
                @coin := 1 
        end) AS coin,
        # ----只需要改动这里的逻辑，其他不要动-----------


        @prev_date := signin_date,
        @prev_uid := uid
    FROM
        (SELECT @prev_date := NULL, @prev_uid := NULL, @consec_days := 1 , @coin := 1) vars,
        (SELECT signin_date, uid FROM t1 ORDER BY uid, signin_date) ordered_dates
)
,
# 每个用户每日金币持有数
`每个用户每日金币持有数` as(
select signin_date,uid,coin,
row_number() over(partition by uid,date_format(signin_date,"%Y-%m") order by signin_date desc) as rn
from signin_dates
)
,
# 每个用户每月底金币持有数
`每个用户每月底金币持有数` as(
select uid,
date_format(signin_date,"%Y%m") as month,
cast(coin as unsigned) as coin
from `每个用户每日金币持有数`
where rn  = 1 
order by month , uid   
)
,
tmptmp as(
    select *,row_number() over(partition by uid order by month asc) as my_rn
    from `每个用户每月底金币持有数`
)

# 自联结求一下每月拿了多少金币
select 
tmp1.uid,tmp1.month,
ifnull(tmp1.coin-tmp2.coin ,tmp1.coin)
from tmptmp tmp1
left join tmptmp tmp2
on tmp1.uid=tmp2.uid and tmp2.my_rn=tmp1.my_rn-1