连续签到领金币 （技巧型）

with coin_table as (
    select
        uid
        ,date_format(dt,'%Y%m') as month
        ,case row_number()over(partition by uid, rk_tag order by dt)%7
            when 3 then 3
            when 0 then 7
            else 1 end as coin_eachday
    from (
        select
            uid
            ,dt
            ,date_sub(dt,interval rk day) rk_tag
        from (
            select
                uid
                ,date(in_time) dt
                ,row_number()over(partition by uid order by date(in_time)) rk
            from tb_user_log
            where artical_id=0 and sign_in=1
            and date(in_time) between '2021-07-07' and '2021-10-31'
        ) t
    ) t
)

select
    uid
    ,month
    ,sum(coin_eachday) coin
from coin_table
group by 1,2
order by 2,1

关于连续第几天

1. 假设一组日期数据为[0,1,2,4,7,8]，我们想按照连续性分组为[0,1,2]、[4]、[7,8]
2. 我们先做序号列[1,2,3,4,5,6]（通过RANK OVER(uid)实现）
3. 然后序号-日期数据=[1,1,1,0,-2,-2]，得到能实现连续性分组的特征标签（即上面的三个分组111，0，-2-2）
4. 按分组再加序号[1,2,3,1,1,2]（通过RANK OVER(uid,特征标签) 实现）（每组内的排序序号，即第几个连续天）