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

select 
    uid,
    month,
    sum(day_coin)
from(
    select 
        uid,
        month,
        case when cday%7 = 3 then sign_in+2
        when cday%7 = 0 then sign_in+6
        else sign_in end as day_coin
    from(
        select 
            uid,
            sign_in,
            date_format(dt,'%Y%m') as month,
            dense_rank() over (partition by date_sub(dt,interval rk day) order by dt) as cday
        from(
            select 
                distinct uid, #一天签到多次的只保留一次
                sign_in,
                date(in_time) as dt,
                dense_rank() over (partition by uid order by date(in_time)) as rk
            from 
                tb_user_log
            where 
                date(in_time) between '2021-07-07' #坑死了这里写7-01不通过
                and  '2021-10-31'
                and sign_in = 1
                and artical_id= 0
            order by uid) as a) as b) as c
group by uid,month
order by month,uid