Database work - Fix issues with affiliated committee data

[tmpayton]

What we’re after

There are data issues blocking the creation of an affiliated committee mv. When we union the three views that contain affiliated committee data there are duplicates. This is due to differences in the data between the views. The research ticket contains an example and an excel spreadsheet that lists the 79 duplicates.

Related ticket(s)

• Affiliated committee data issues  #5856
• Add affiliated committees to the /committees endpoint #5394

Action item(s)

• Fix data issues that are causing duplicates within the affiliated committee data

Completion criteria

• There are no duplicates with affiliated committee data when unioning fec_form_1s_vw, fec_vsum_f1_vw, and fec_vsum_f1z_vw

References/resources/technical considerations

Sql snippet that identifies duplicates:

with aff_cmte_data as 
(SELECT fec_vsum_f1_vw.cmte_id,fec_vsum_f1_vw.affiliated_cmte_id, fec_vsum_f1_vw.affiliated_cmte_nm, fec_vsum_f1_vw.receipt_dt, fec_vsum_f1_vw.affiliated_relationship_cd
           FROM fec_vsum_f1_vw
          WHERE fec_vsum_f1_vw.affiliated_cmte_id IS NOT NULL AND fec_vsum_f1_vw.most_recent = 'Y'
		UNION        
         SELECT fec_form_1s_vw.cmte_id, fec_form_1s_vw.affiliated_cmte_id, fec_form_1s_vw.affiliated_cmte_nm, fec_form_1s_vw.receipt_dt, fec_form_1s_vw.affiliated_relationship_cd
           FROM fec_form_1s_vw
          WHERE fec_form_1s_vw.affiliated_cmte_id IS NOT NULL AND fec_form_1s_vw.most_recent_filing_flag = 'Y'
		UNION
         SELECT fec_vsum_f1z_vw.cmte_id, fec_vsum_f1z_vw.affiliated_cmte_id, fec_vsum_f1z_vw.affiliated_cmte_nm, fec_vsum_f1z_vw.receipt_dt, fec_vsum_f1z_vw.affiliated_relationship_cd
           FROM public.fec_vsum_f1z_vw
          WHERE fec_vsum_f1z_vw.affiliated_cmte_id IS NOT null AND fec_vsum_f1z_vw.most_recent = 'Y')
 select cmte_id, affiliated_cmte_id, count(*) from aff_cmte_data group by cmte_id, affiliated_cmte_id having count(*) > 1;