题解 | 连续签到领金币

with t1 as (
    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)
)

select 
    uid,
    date_format(dt,'%Y%m'),
    sum(coin) as coin
from (
    select
        uid,
        dt,
        case 
            when continue_days % 7 = 3 then 3
            when continue_days % 7 = 0 then 7
            else 1
        end as coin
    from (
        select
            uid,
            dt,
            dense_rank() over (partition by diff_dt,uid order by dt) as continue_days
        from (
            select
                uid,
                dt,
                date_sub(dt,interval rn day) as diff_dt
            from (
                select
                    *,
                    dense_rank() over (partition by uid order by dt) as rn
                from 
                    t1
            ) as t2
        ) as t3
    ) as t4
) as t5
group by 
    uid,
    date_format(dt,'%Y%m')
order by 
    date_format(dt,'%Y%m'),
    uid