<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Campaign Finance Disclosure Filings
Setup
Step1:  Cover Sheets
Every campaign finance disclosure filing has a cover sheet, and the top-level information from these cover sheets ends up in the CVR_CAMPAIGN_DISCLOSURE_CD. This is an important table because it links because it links the dollar amount totals from the SMRY_CD table to a name fields and a unique identifier of the filer.
The forms of the filings that end up in CVR_CAMPAIGN_DISCLOSURE_CD are all the ones that include financial disclosures of campaigns as opposed to statements of intention and organization.
Do the FORM_TYPE values vary between amendments to the same any campaign filing?
No, which is good because we can easily sort records linked to the FILING_ID and AMEND_ID combinations.
Step2:  Do the FILER_ID values vary between amendments to the same any campaign filing?
No, which is good because we should then be able to sort out who filed which filings.
Step3:  Joining to FILER_FILINGS_CD
The FILER_FILINGS_CD has additional information about each filing, including the filing period in which the filing files. 
There are also lot of fields that seem to be redundant with fields on CVR_CAMPAIGN_DISCLOSURE_CD, specifically
Step4:  Do any records have conflicting CVR_CAMPAIGN_DISCLOSURE_CD.FORM_TYPE and FILER_FILINGS_CD.FORM_ID values?
They do, but not a lot. And there is a pretty clear pattern with CVR_CAMPAIGN_DISCLOSURE_CD.FORM_TYPE saying F497 and FILER_FILINGS_CD.FORM_ID saying F496.
Step5:  Do any records have conflicting CVR_CAMPAIGN_DISCLOSURE_CD.FILER_ID and FILER_FILINGS_CD.FILER_ID values?
Yes, this happens about a third of the time.
Step6:  But one thing to note is that these FILER_ID fields are too different data types
Step7:  Should probably look more into these later, but this might have something to do with conflicting filer_ids on CVR_CAMPAIGN_DISCLOSURE_CD and FILER_FILINGS_CD.
Does the filer_id from FILER_XREF_CD and FILER_FILINGS_CD ever conflict for the same filing?
Yes, but less than 1 percent of the time.
Step8:  Mostly this seems to be a problem for Form 497 filings.
Step9:  Does the FILER_FILINGS_CD.FORM_ID value ever vary between amendments to the same filing?
I guess there always has to be at least one.
Step10:  But there aren't any CVR_CAMPAIGN_DISCLOSURE_CD or SMRY_CD records for this filing_id, so maybe it isn't real.
Step11:  Does the FILER_FILINGS_CD.FILER_ID value ever vary between amendments to the same filing?
Even when we narrow to only the campaign finance-related forms, the answer is "yes".
<ASSISTANT_TASK:>
Python Code:
%load_ext sql
from django.conf import settings
connection_string = 'postgresql+psycopg2://{USER}:{PASSWORD}@{HOST}:{PORT}/{NAME}'.format(
    **settings.DATABASES['default']
)
%sql $connection_string
Explanation: Campaign Finance Disclosure Filings
Setup
End of explanation
%%sql
SELECT cvr."FILING_ID", COUNT(DISTINCT cvr."FORM_TYPE")
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" cvr
JOIN "FILER_FILINGS_CD" ff
ON cvr."FILING_ID" = ff."FILING_ID"
AND cvr."AMEND_ID" = ff."FILING_SEQUENCE"
GROUP BY 1
HAVING COUNT(DISTINCT cvr."FORM_TYPE") > 1;
Explanation: Cover Sheets
Every campaign finance disclosure filing has a cover sheet, and the top-level information from these cover sheets ends up in the CVR_CAMPAIGN_DISCLOSURE_CD. This is an important table because it links because it links the dollar amount totals from the SMRY_CD table to a name fields and a unique identifier of the filer.
The forms of the filings that end up in CVR_CAMPAIGN_DISCLOSURE_CD are all the ones that include financial disclosures of campaigns as opposed to statements of intention and organization.
Do the FORM_TYPE values vary between amendments to the same any campaign filing?
No, which is good because we can easily sort records linked to the FILING_ID and AMEND_ID combinations.
End of explanation
%%sql
SELECT "FILING_ID", COUNT(DISTINCT "FILER_ID")
FROM "CVR_CAMPAIGN_DISCLOSURE_CD"
GROUP BY 1
HAVING COUNT(DISTINCT "FILER_ID") > 1
ORDER BY COUNT(DISTINCT "FILER_ID") DESC;
Explanation: Do the FILER_ID values vary between amendments to the same any campaign filing?
No, which is good because we should then be able to sort out who filed which filings.
End of explanation
%%sql
SELECT cvr."FORM_TYPE", cvr."FILING_ID", cvr."AMEND_ID", cvr."FILER_ID", cvr."FILER_NAML", cvr."RPT_DATE", *
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" cvr
LEFT JOIN "FILER_FILINGS_CD" ff
ON cvr."FILING_ID" = ff."FILING_ID"
AND cvr."AMEND_ID" = ff."FILING_SEQUENCE"
WHERE ff."FILING_ID" IS NULL or ff."FILING_SEQUENCE" IS NULL;
Explanation: Joining to FILER_FILINGS_CD
The FILER_FILINGS_CD has additional information about each filing, including the filing period in which the filing files. 
There are also lot of fields that seem to be redundant with fields on CVR_CAMPAIGN_DISCLOSURE_CD, specifically:
* CVR_CAMPAIGN_DISCLOSURE_CD.FORM_TYPE and FILER_FILINGS_CD.FORM_ID
* CVR_CAMPAIGN_DISCLOSURE_CD.FILER_ID and FILER_FILINGS_CD.FILER_ID
* CVR_CAMPAIGN_DISCLOSURE_CD.STMT_TYPE and FILER_FILINGS_CD.STMNT_TYPE
* CVR_CAMPAIGN_DISCLOSURE_CD.RPT_DATE and FILER_FILINGS_CD.FILING_DATE
* CVR_CAMPAIGN_DISCLOSURE_CD.FROM_START and FILER_FILINGS_CD.RPT_START
* CVR_CAMPAIGN_DISCLOSURE_CD.THRU_DATE and FILER_FILINGS_CD.RPT_END
* CVR_CAMPAIGN_DISCLOSURE_CD.RPT_DATE and FILER_FILINGS_CD.RPT_DATE
Might be worth checking if values in each pair of fields ever conflict.
Does every CVR_CAMPAIGN_DISCLOSURE_CD record have a FILER_FILINGS_CD record?
Almost. And among records left on the FILER_FILINGS_CD table, many of the fields or blank of have values that suggest they are only for testing purposes.
End of explanation
%%sql
SELECT cvr."FORM_TYPE", ff."FORM_ID", cvr."FILING_ID", cvr."AMEND_ID", cvr."RPT_DATE", *
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" cvr
JOIN "FILER_FILINGS_CD" ff
ON cvr."FILING_ID" = ff."FILING_ID"
AND cvr."AMEND_ID" = ff."FILING_SEQUENCE"
WHERE UPPER(cvr."FORM_TYPE") <> UPPER(ff."FORM_ID")
ORDER BY cvr."RPT_DATE" DESC, cvr."FILING_ID" DESC, cvr."AMEND_ID" DESC;
Explanation: Do any records have conflicting CVR_CAMPAIGN_DISCLOSURE_CD.FORM_TYPE and FILER_FILINGS_CD.FORM_ID values?
They do, but not a lot. And there is a pretty clear pattern with CVR_CAMPAIGN_DISCLOSURE_CD.FORM_TYPE saying F497 and FILER_FILINGS_CD.FORM_ID saying F496.
End of explanation
%%sql
SELECT COUNT(*)::float / (
        SELECT COUNT(*)
        FROM "CVR_CAMPAIGN_DISCLOSURE_CD" CVR
        JOIN "FILER_FILINGS_CD" FF
        ON CVR."FILING_ID" = FF."FILING_ID"
        AND CVR."AMEND_ID" = FF."FILING_SEQUENCE"
) as pct_conflict
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" CVR
JOIN "FILER_FILINGS_CD" FF
ON CVR."FILING_ID" = FF."FILING_ID"
AND CVR."AMEND_ID" = FF."FILING_SEQUENCE"
WHERE CVR."FILER_ID" <> FF."FILER_ID"::VARCHAR;
Explanation: Do any records have conflicting CVR_CAMPAIGN_DISCLOSURE_CD.FILER_ID and FILER_FILINGS_CD.FILER_ID values?
Yes, this happens about a third of the time.
End of explanation
%%sql
SELECT 
    cvr."FILER_ID" as cvr_filer_id, 
    fn."FILER_ID" as filername_filer_id, 
    f."FILER_ID" as filer_filer_id
FROM (
    SELECT DISTINCT cvr."FILER_ID"
    FROM "CVR_CAMPAIGN_DISCLOSURE_CD" cvr
    LEFT JOIN "FILER_XREF_CD" x
    ON cvr."FILER_ID" = x."XREF_ID"
    WHERE x."XREF_ID" IS NULL
) cvr
LEFT JOIN "FILERNAME_CD" fn
ON cvr."FILER_ID" = fn."FILER_ID"::varchar
LEFT JOIN "FILERS_CD" f
ON cvr."FILER_ID" = f."FILER_ID"::varchar
ORDER BY cvr."FILER_ID"::VARCHAR DESC;
Explanation: But one thing to note is that these FILER_ID fields are too different data types: char on CVR_CAMPAIGN_DISCLOSURE_CD and int on FILER_FILINGS_CD. We previously discovered that the FILER_XREF_CD table is a translator from seemingly old string filer_ids to numeric filer_ids. 
Does every CVR_CAMPAIGN_DISCLOSURE_CD record have a FILER_XREF_ID record?
No. Here are the missing filer_ids, which are also not found in either the FILERNAME_CD or FILERS_CD tables.
End of explanation
%%sql
SELECT COUNT(*)::float / (
        SELECT COUNT(*)
        FROM "CVR_CAMPAIGN_DISCLOSURE_CD" CVR
        JOIN "FILER_FILINGS_CD" FF
        ON CVR."FILING_ID" = FF."FILING_ID"
        AND CVR."AMEND_ID" = FF."FILING_SEQUENCE"
) as pct_conflict
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" CVR
JOIN "FILER_FILINGS_CD" FF
ON CVR."FILING_ID" = FF."FILING_ID"
AND CVR."AMEND_ID" = FF."FILING_SEQUENCE"
JOIN "FILER_XREF_CD" X
ON CVR."FILER_ID" = X."XREF_ID"
WHERE X."FILER_ID" <> FF."FILER_ID";
Explanation: Should probably look more into these later, but this might have something to do with conflicting filer_ids on CVR_CAMPAIGN_DISCLOSURE_CD and FILER_FILINGS_CD.
Does the filer_id from FILER_XREF_CD and FILER_FILINGS_CD ever conflict for the same filing?
Yes, but less than 1 percent of the time.
End of explanation
%%sql
SELECT cvr."FORM_TYPE", COUNT(*)
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" CVR
JOIN "FILER_FILINGS_CD" FF
ON CVR."FILING_ID" = FF."FILING_ID"
AND CVR."AMEND_ID" = FF."FILING_SEQUENCE"
JOIN "FILER_XREF_CD" X
ON CVR."FILER_ID" = X."XREF_ID"
WHERE X."FILER_ID" <> FF."FILER_ID"
GROUP BY 1;
Explanation: Mostly this seems to be a problem for Form 497 filings.
End of explanation
%%sql
SELECT "FILING_ID", COUNT(DISTINCT "FORM_ID")
FROM "FILER_FILINGS_CD"
WHERE "FORM_ID" IN (
    SELECT DISTINCT "FORM_TYPE"
    FROM "CVR_CAMPAIGN_DISCLOSURE_CD"
)
GROUP BY 1
HAVING COUNT(DISTINCT "FORM_ID") > 1
ORDER BY 1 DESC;
%%sql
SELECT *
FROM "FILER_FILINGS_CD"
WHERE "FILING_ID" = 826532;
Explanation: Does the FILER_FILINGS_CD.FORM_ID value ever vary between amendments to the same filing?
I guess there always has to be at least one.
End of explanation
%%sql
SELECT *
FROM "CVR_CAMPAIGN_DISCLOSURE_CD"
WHERE "FILING_ID" = 826532;
SELECT *
FROM "SMRY_CD"
WHERE "FILING_ID" = 826532;
Explanation: But there aren't any CVR_CAMPAIGN_DISCLOSURE_CD or SMRY_CD records for this filing_id, so maybe it isn't real.
End of explanation
%%sql
SELECT "FILING_ID", COUNT(DISTINCT "FILING_SEQUENCE"), COUNT(DISTINCT "FILER_ID")
FROM "FILER_FILINGS_CD"
WHERE "FORM_ID" IN (
    SELECT DISTINCT "FORM_TYPE"
    FROM "CVR_CAMPAIGN_DISCLOSURE_CD"
)
GROUP BY 1
HAVING COUNT(DISTINCT "FILER_ID") > 1
ORDER BY 1 DESC;
Explanation: Does the FILER_FILINGS_CD.FILER_ID value ever vary between amendments to the same filing?
Even when we narrow to only the campaign finance-related forms, the answer is "yes".
End of explanation
<END_TASK>