sum aggregates by election

summarize_aggregates.sql

with cycles as (
    select
        agg.*,
        link.election_year
    from ofec_sched_a_aggregate_state agg
    join ofec_name_linkage_mv link on
        agg.cmte_id = link.committee_id and
        agg.cycle <= link.election_year and
        agg.cycle > link.election_year -
            case link.committee_type
                when 'S' then 6
                when 'P' then 4
                else 2
            end
)
select
    cmte_id,
    election_year,
    state,
    max(state_full) as state_full,
    sum(total) as total,
    sum(count) as count
from cycles
group by
    cmte_id,
    election_year,
    state
;