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

select
    uid,
    date_format(dt,"%Y%m") as month_,
    sum(have_money) as coin
from
(
    select
        uid,
        dt,
        case 
            when rn%7 = 3 then 3
            when rn%7 = 0 then 7
            else 1 
            end as have_money
    from
    (
        select
            uid,
            dt,
            dense_rank()over(partition by cut_dt order by dt) as rn
        from
        (
            select
                uid,
                dt,
                date_sub(dt, interval cut day) as cut_dt
            from
            (
                select
                    uid,
                    dt,
                    dense_rank()over(partition by uid order by dt) as cut
                from
                (
                    select distinct
                        uid,
                        date(in_time) as dt
                    from tb_user_log
                    where date(in_time) between "2021-07-07" and "2021-10-31"
                        and artical_id = 0 and sign_in = 1
                    order by uid,dt
                ) as tb_1
            ) as tb_2
        ) as tb_3
    ) as tb_4
) as tb_5
group by uid,month_
order by month_,uid