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

#6、直接按要求聚合就行了
select 
    uid,
    YM,
    sum(nums) `coin`
from(
    #5、根据排名来确定每天该用户登录该领取的金币数
    select 
        *,
    # if(rk%7<3,rk+round((rk/7)*15,0),if(rk%7<7,rk+2+round((rk/7)*15,0),rk/7*15)) `nums`
    if(rk%7=3,3,if(rk%7=0,7,1)) `nums`
    from(
        # 4、排名每个会话内的天数
        select 
            uid,
            date_format(dt,'%Y%m') 'YM',
            dt,
            session ,
            row_number() over(partition by uid,session) rk
        from(
            #3、设置会话
            select 
                *,
                sum(flag) over(partition by uid rows between unbounded preceding and current row)+1 `session`
            from(
                # 2、连续登录的打上标记
                select
                    *,
                    lag(dt,1,dt) over(partition by uid order by dt) `last_sign`,
                    if(dt-lag(dt,1,dt) over(partition by uid order by dt)>1,1,0) `flag`
                from(
                    #1、获取用户在活动期间签到记录
                    select 
                        uid,
                        date(in_time) `dt`
                    from tb_user_log
                    where datediff(date(in_time),'2021-07-07')>=0 and in_time<'2021-11-01' and artical_id=0 and sign_in=1
                )t1
            )t2
        )t3
    )t4
)t5
group by uid,YM