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

select t2.uid,DATE_FORMAT(t2.dt,'%Y%m') month,sum(t2.coin) coin
from(
    -- 计算用户当天的连续签到数和领取的金币数
        select *,DENSE_RANK() over(partition by t1.uid,t1.dt2 order by t1.dt) day_rk, -- 计算当天连续签到天数,按照用户id和日期-排名的差来分组
                        -- 当天连续签到天数%7=3 则领取3金币
                        -- 当天连续签到天数%7=0 则领取7金币
                        -- 其余情况，领取1金币
                        (case when DENSE_RANK() over(partition by t1.uid,t1.dt2 order by t1.dt)%7=3 then 3
                                 when DENSE_RANK() over(partition by t1.uid,t1.dt2 order by t1.dt)%7=0 then 7
                        else 1 end ) coin
        from(
            -- 计算用户每天的签到表并对签到表按时间进行排序
                select uid,date(in_time) dt,DENSE_RANK() over(partition by uid order by date(in_time)) rk,
                             -- 如果签到日期是连续的，则签到日期-排序得到的差相同
                             date(in_time)-DENSE_RANK() over(partition by uid order by date(in_time)) 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

        )t1
)t2
group by t2.uid,DATE_FORMAT(t2.dt,'%Y%m')
order by t2.uid,DATE_FORMAT(t2.dt,'%Y%m')