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

select 
    uid,
    date_format(first_day,'%Y%m') as month,
    sum(case when mcnt%7=3 then 3 when mcnt%7=0 then 7 else 1 end) as coin
from(
        select 
            uid,
            dt-rk as first_day,
            count(1) over (partition by uid,dt-rk order by dt asc) as mcnt 
        from(
            select 
                uid,
                date(in_time) as dt,
                rank() over (partition by uid order by date(in_time) asc) as rk
            from 
                tb_user_log
            where
                date(in_time) >= '2021-07-07'
                and date(in_time) <= '2021-10-31'
                and artical_id = 0
                and sign_in = 1) as t) as t2
group by 1,2
order by month,uid asc