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

#我这种办法和题解的办法都存在一个bug 就是在月底和新月的连续签到金币无法计算，比如说月底连续签到了6天 下一个月第一天签到按道理应该领取6金币 我这种写法就不行了 虽然过是过了 但还是想问有哪位大佬能帮我解决下这个问题吗 （tip：我这个方法是自己想的求连续天数的方法，结合了case when和sum开窗 比较巧妙 当然感觉不如传统的排序做差法好理解）

select A4.uid
,A4.dat dat2
,sum(A4.grp4) grp5
from(
    select A3.uid,A3.grp3,date_format(in_time,'%Y%m') dat,floor(count(*)/7)*15+if(count(*)%7>=3,(count(*)%7)+2,count(*)%7) grp4
    from(
        select A2.uid,A2.in_time,sum(A2.grp2)over(order by A2.id) grp3
        from(
            select A1.grp1 grp1
            ,A1.uid uid
            ,A1.id id
            ,A1.in_time
            ,case when A1.grp1 = 0 then 1 else 0 end grp2
            from(

                select uid
                ,in_time
                ,id
                ,if(ifnull(datediff(date_format(in_time,'%Y-%m-%d'),date_format(ifnull(lag(in_time,1)over(partition by uid order by in_time asc),in_time),'%Y-%m-%d')),0)<>1,0,1) grp1       
                from tb_user_log
                where in_time between '2021-07-07 0:00:00' and '2021-10-31 23:59:59'
                  and artical_id=0
                  and sign_in=1
            )as A1   
        )as A2
    )as A3
    group by A3.uid,A3.grp3,dat
 )as A4 
group by A4.uid,A4.dat
order by A4.uid asc