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

with
    t2 as (
        select
            uid,
            time,
            time - rank() over (
                partition by
                    uid
                order by
                    time
            ) rk
        from
            (
                select
                    uid,
                    date (in_time) as time
                from
                    tb_user_log
                where
                    artical_id = 0
                    and sign_in = 1
            ) t1
        where
            date (time) between '2021-07-07' and '2021-10-31'
    )
select
    uid,
    month,
    sum(money) as coin
from
    (
        select
            uid,
            left (time + 1 -1, 6) as month,
            (
                case
                    when rk1 = 0 then 7
                    when rk1 = 3 then 3
                    else 1
                end
            ) as money
        from
            (
                select
                    uid,
                    time,
                    row_number() over (
                        partition by
                            uid,
                            rk
                        order by
                            time
                    ) mod 7 as rk1
                from
                    t2
            ) t3
    ) t4
group by
    uid,
    month
order by
    month,
    uid