with NumbersCTE as (
    select 'a' as cat, 1 as number
    union all
    select
        case
            when number < 3 then 'a'
            when number < 6 then 'b'
            else 'c'
        end as cat,
        number + 1
    from NumbersCTE
    where number < 10)
select
    cat,
    number,
    case
        when cat != LAG(cat, 1, '') over (order by cat, number) then 0
        else LAG(running_total, 1, 0) over (order by cat, number) + LAG(number, 1, 0) over (order by cat, number)
    end as running_total
from NumbersCTE
order by cat, number;