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

select uid, dt2 as month , sum(core)from (
select uid,date_format(dt,'%Y%m') dt2,subnumrank,if(subnumrank % 7 = 3 , 3, if(subnumrank % 7 = 0, 7,1)) as core from (
select uid,dt,date_sub(dt,interval ranknum DAY) as dt2,dense_rank()over(partition by uid,date_sub(dt,interval ranknum DAY) order by dt) as subnumrank  from (
select
    uid,
    date (in_time) as dt,
    dense_rank() over (
        partition by
            uid
        order by
            in_time
    ) as ranknum
from
    tb_user_log
where
    date (in_time) between '2021-07-07' and '2021-10-31'
    And sign_in = 1 and artical_id = 0
) as t1 # t1表只是求出符合要求的uid 和 in_time时间
) t2 # t2表是根据客户的in_time和签到次数做一个相减判定，如果是连续签到的，那么签到时间减去派排名就是一个固定的值，就是第一天签到的前一天如果是7月7日签到第一天，那么这个值就是7月6日
) t3
group by uid,month

本体难度很大，如果要求客户是否连续签到需要用in_time的值和dense_rank()over()的值进行一个相减，然后再根据相减后的值进行分类排序即可。还需要判断article_id 不为0.