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

select uid, month, sum(coin) as coin
from
(
    select uid, month, tmp,
    case
        when cnt%7<3 and cnt%7>0 then floor(cnt/7)*15+cnt%7
        when cnt%7>=3  then floor(cnt/7)*15+cnt%7+2
        when cnt%7=0 then floor(cnt/7)*15
    end as coin
    from (
        select uid, month, tmp, count(tmp) as cnt
        from (
        select uid, month, dt-rn as tmp
        from (
        select uid, date(in_time) as dt, row_number() over(partition by uid order by in_time) as rn, date_format(in_time, '%Y%m') as month
        from tb_user_log
        where sign_in=1 and artical_id=0 and date(in_time) between '2021-07-07' and '2021-10-31'
        ) t1
        ) t2
        group by uid, month, tmp
    ) t3
) t4
group by uid, month
order by month, uid;