[presidential map] test consistency of api endpoints

[jason-upchurch]

Summary

do the new endpoints provide results consistent with data available through classic?

Completion criteria

• determine if consistency is acceptable

Technical considerations

• what metric of performance should we use? What are acceptable thresholds for this metric?

[lbeaufort]

Because all the logic is on the SQL side, all we really need to do is make sure the outputs from the original queries match the outputs of the views. We didn't make many changes to the starting logic - I think the exception is /by_candidate/. Here's a proof of concept for national totals:

--Make sure all the row values are the same

WITH query1 as (SELECT distinct 
    cand_id AS candidate_id, 
    --cand_nm AS candidate_name,
    SUBSTR(cand_nm, 0, strpos(cand_nm,',')) AS candidate_last_name, 
    --(CASE WHEN cand_pty_affiliation IS NULL THEN NULL ELSE '('||SUBSTR(CAND_PTY_AFFILIATION,1,1)||')' END) AS cand_pty_affiliation, 
    ROUND(((INDV_CONTB_PER - REF_INDV_CONTB_PER) + 
       (POL_PTY_CMTE_CONTB_PER - REF_POL_PTY_CMTE_CONTB_PER) + 
       (OTHER_POL_CMTE_CONTB_PER - REF_OTHER_POL_CMTE_CONTB_PER) + 
       CAND_CONTB_PER + TRANF_FROM_AFFILATED_CMTE_PER + 
       (LOANS_RECEIVED_FROM_CAND_PER - REPYMTS_LOANS_MADE_BY_CAND_PER) + 
       (OTHER_LOANS_RECEIVED_PER - REPYMTS_OTHER_LOANS_PER) + OTHER_RECEIPTS_PER 
       + COALESCE(FED_FUNDS_PER,0))/1000000,1) AS net_receipts,
    2020 as election_year
    FROM DISCLOSURE.PRES_F3P_TOTALS_CA_CM_LINK_20D
    UNION
    SELECT distinct 
    cand_id AS candidate_id, 
    --cand_nm AS candidate_name,
    SUBSTR(cand_nm, 0, strpos(cand_nm,',')) AS candidate_last_name, 
    --(CASE WHEN cand_pty_affiliation IS NULL THEN NULL ELSE '('||SUBSTR(CAND_PTY_AFFILIATION,1,1)||')' END) AS cand_pty_affiliation, 
    ROUND(((INDV_CONTB_PER - REF_INDV_CONTB_PER) + 
       (POL_PTY_CMTE_CONTB_PER - REF_POL_PTY_CMTE_CONTB_PER) + 
       (OTHER_POL_CMTE_CONTB_PER - REF_OTHER_POL_CMTE_CONTB_PER) + 
       CAND_CONTB_PER + TRANF_FROM_AFFILATED_CMTE_PER + 
       (LOANS_RECEIVED_FROM_CAND_PER - REPYMTS_LOANS_MADE_BY_CAND_PER) + 
       (OTHER_LOANS_RECEIVED_PER - REPYMTS_OTHER_LOANS_PER) + OTHER_RECEIPTS_PER 
       + COALESCE(FED_FUNDS_PER,0))/1000000,1) AS net_receipts,
    2016 as election_year 
    FROM DISCLOSURE.PRES_F3P_TOTALS_CA_CM_LINK_16
    ORDER BY election_year, net_receipts DESC),
query2 as (SELECT candidate_id, candidate_last_name, rounded_net_receipts, election_year FROM public.ofec_presidential_by_candidate_vw
WHERE contributor_state='US')
(select * from query1 EXCEPT select * from query2) 
UNION ALL
(select * from query2 EXCEPT select * from query1)

-- Make sure the counts are the same
WITH query1 as (SELECT distinct 
    cand_id AS candidate_id, 
    --cand_nm AS candidate_name,
    SUBSTR(cand_nm, 0, strpos(cand_nm,',')) AS candidate_last_name, 
    --(CASE WHEN cand_pty_affiliation IS NULL THEN NULL ELSE '('||SUBSTR(CAND_PTY_AFFILIATION,1,1)||')' END) AS cand_pty_affiliation, 
    ROUND(((INDV_CONTB_PER - REF_INDV_CONTB_PER) + 
       (POL_PTY_CMTE_CONTB_PER - REF_POL_PTY_CMTE_CONTB_PER) + 
       (OTHER_POL_CMTE_CONTB_PER - REF_OTHER_POL_CMTE_CONTB_PER) + 
       CAND_CONTB_PER + TRANF_FROM_AFFILATED_CMTE_PER + 
       (LOANS_RECEIVED_FROM_CAND_PER - REPYMTS_LOANS_MADE_BY_CAND_PER) + 
       (OTHER_LOANS_RECEIVED_PER - REPYMTS_OTHER_LOANS_PER) + OTHER_RECEIPTS_PER 
       + COALESCE(FED_FUNDS_PER,0))/1000000,1) AS net_receipts,
    2020 as election_year
    FROM DISCLOSURE.PRES_F3P_TOTALS_CA_CM_LINK_20D
    UNION
    SELECT distinct 
    cand_id AS candidate_id, 
    --cand_nm AS candidate_name,
    SUBSTR(cand_nm, 0, strpos(cand_nm,',')) AS candidate_last_name, 
    --(CASE WHEN cand_pty_affiliation IS NULL THEN NULL ELSE '('||SUBSTR(CAND_PTY_AFFILIATION,1,1)||')' END) AS cand_pty_affiliation, 
    ROUND(((INDV_CONTB_PER - REF_INDV_CONTB_PER) + 
       (POL_PTY_CMTE_CONTB_PER - REF_POL_PTY_CMTE_CONTB_PER) + 
       (OTHER_POL_CMTE_CONTB_PER - REF_OTHER_POL_CMTE_CONTB_PER) + 
       CAND_CONTB_PER + TRANF_FROM_AFFILATED_CMTE_PER + 
       (LOANS_RECEIVED_FROM_CAND_PER - REPYMTS_LOANS_MADE_BY_CAND_PER) + 
       (OTHER_LOANS_RECEIVED_PER - REPYMTS_OTHER_LOANS_PER) + OTHER_RECEIPTS_PER 
       + COALESCE(FED_FUNDS_PER,0))/1000000,1) AS net_receipts,
    2016 as election_year 
    FROM DISCLOSURE.PRES_F3P_TOTALS_CA_CM_LINK_16
    ORDER BY election_year, net_receipts DESC),
query2 as (SELECT candidate_id, candidate_last_name, rounded_net_receipts, election_year FROM public.ofec_presidential_by_candidate_vw
WHERE contributor_state='US')
select (select count(*)
FROM query1) - (
select count(*)
FROM query2)

[lbeaufort]

For per-state totals, ok:

WITH query1 as (SELECT CAND_ID as candidate_id,
    --CAND_PTY_AFFILIATION as candidate_party_affiliation, --changed the format from (D) to DEM
    CAND_NM  as candidate_last_name, 
    SUM(NET_RECEIPTS_STATE ) as net_receipts, -- no longer rounding
    contbr_st AS contributor_state,
    2020 as election_year
    FROM DISCLOSURE.PRES_CA_CM_SCHED_STATE_20D
    GROUP BY cand_id,Cand_nm, contbr_st
    UNION
    SELECT CAND_ID as candidate_id,
    --CAND_PTY_AFFILIATION as candidate_party_affiliation, 
    CAND_NM  as candidate_last_name, 
    SUM(NET_RECEIPTS_STATE ) as net_receipts, -- no longer rounding
    contbr_st AS contributor_state,
    2016 as election_year
    FROM DISCLOSURE.PRES_CA_CM_SCHED_STATE_16
    GROUP BY candidate_id,candidate_last_name, contributor_state
    ORDER BY candidate_id,candidate_last_name, contributor_state),
query2 as (
    SELECT candidate_id, candidate_last_name, net_receipts, contributor_state, election_year FROM public.ofec_presidential_by_candidate_vw
    WHERE contributor_state!='US'
    ORDER BY candidate_id,candidate_last_name, contributor_state)
select (select count(*)
FROM query1) - (
select count(*)
FROM query2)

[lbeaufort]

All the other endpoints pull directly from the base tables without significant changes to the original classic logic, so I think we're ok to close this ticket. Any filter bugs, etc will be easy to address.