Individual contributions by state aggregate doesn't equal sum of transactions

[PaulClark2]

Bug

Individual contributions by state aggregate doesn't equal the sum of supporting transactions.

Expected Behavior

Individual contributions by state aggregate should equal the sum of supporting transactions.

How to Reproduce

I'm looking at individual contributions by state for C00695510 ( https://www.fec.gov/data/committee/C00695510/?cycle=2020&tab=raising). Select state then look at California, $1,230,898.16. Now click through to the supporting transactions and download them. Sum contribution_receipt_amount (column AH), $872,589.26.

I checked three additional committees. Two, C00698258 (checked CO) and C00697441 (checked CA), had equal amounts by state and from the sum of the transactions. One, C00213512 (checked CA), did not have equal amounts by state and from the sum of the transactions

[PaulClark2]

The Individual Contribution data table isn't properly including all 15J transactions. The by state aggregate does properly include 15Js

From the download (described above: https://www.fec.gov/data/committee/C00695510/?cycle=2020&tab=raising). Select state then look at California, $1,230,898.16. Now click through to the supporting transactions and download them. Sum contribution_receipt_amount (column AH), $872,589.26.))

Transaction type	Count	Amount
15	635	$753,797.10
15E	297	$115,687.16
15J	86	$3,105.00
Total	1,018	$872,589.26

From the database
select receipt_tp, count(*), sum(contb_receipt_amt) from disclosure.fec_fitem_sched_a where cmte_id = 'C00695510' and contbr_st = 'CA' and is_individual is true group by receipt_tp;

Transaction type	Count	Amount
15	635	$753,797.10
15E	297	$115,687.16
15J	1,670	$361,413.90
Total	2,602	$1,230,898.16

[jason-upchurch]

/v1/committee/C00695510/schedules/schedule_a/by_state/?state=CA&cycle=2020&sort_nulls_last=false&hide_null=false&per_page=20&sort=-total&sort_hide_null=false&sort_null_only=false&page=1 returns $1,230,898.16

[jason-upchurch]

Adding

WHERE disclosure.fec_fitem_sched_a.contb_receipt_dt >= '1/1/2019' 
AND disclosure.fec_fitem_sched_a.contb_receipt_dt <= '12/31/2020'

to the query:

select receipt_tp, count(*), sum(contb_receipt_amt) 
from disclosure.fec_fitem_sched_a 
where cmte_id = 'C00695510' 
and contbr_st = 'CA' and is_individual is true group by receipt_tp;

yields:

Transaction type	Count	Amount
15	635	$753,797.10
15E	297	$115,687.16
15J	86	$3,105.00

[edited for formatting]

[jason-upchurch]

There are transactions for the 2020 two_year_transaction_period that occurred prior to 1/1/2019:

select contb_receipt_dt, count(*) 
from disclosure.fec_fitem_sched_a 
where two_year_transaction_period = 2020 and receipt_tp = '15J' 
and cmte_id = 'C00695510' 
and contbr_st = 'CA' 
and is_individual is true 
group by contb_receipt_dt order by contb_receipt_dt;

[edited for formatting]

[jason-upchurch]

referencing fecgov/fec-cms#2811 to determine if related / blocking

[jason-upchurch]

Need to write fec-cms pytest as part of this bug to test blocker fecgov/fec-cms#2811

[lbeaufort]

PR has been merged to the release branch