12011 filer_links scenarios

12011_filer_link_scenarios.sql

SELECT 
    'A is a CANDIDATE/OFFICEHOLDER' AS scenario,
     "LINK_TYPE",
    COUNT(DISTINCT A."FILER_ID_A") AS the_count
FROM "FILER_LINKS_CD" A
JOIN "FILER_TO_FILER_TYPE_CD" B
ON A."FILER_ID_A" = B."FILER_ID"
AND B."FILER_TYPE" = 8 -- code for CANDIDATE/OFFICEHOLDER
WHERE "LINK_TYPE" = 12011
GROUP BY 1, 2
UNION
SELECT 
    'A is a RECIPIENT COMMITTEE' AS scenario,
     "LINK_TYPE",
    COUNT(DISTINCT A."FILER_ID_A") AS the_count
FROM "FILER_LINKS_CD" A
JOIN "FILER_TO_FILER_TYPE_CD" B
ON A."FILER_ID_A" = B."FILER_ID"
AND B."FILER_TYPE" = 16 -- code for RECIPIENT COMMITTEE
WHERE "LINK_TYPE" = 12011
GROUP BY 1, 2
UNION
SELECT 
    'B is a CANDIDATE/OFFICEHOLDER' AS scenario,
    "LINK_TYPE",
    COUNT(DISTINCT A."FILER_ID_B") AS the_count
FROM "FILER_LINKS_CD" A
JOIN "FILER_TO_FILER_TYPE_CD" B
ON A."FILER_ID_B" = B."FILER_ID"
AND B."FILER_TYPE" = 8 -- code for CANDIDATE/OFFICEHOLDER
WHERE "LINK_TYPE" = -12011
GROUP BY 1, 2
UNION
SELECT 
    'B is a RECIPIENT COMMITTEE' AS scenario,
    "LINK_TYPE",
    COUNT(DISTINCT A."FILER_ID_B") AS the_count
FROM "FILER_LINKS_CD" A
JOIN "FILER_TO_FILER_TYPE_CD" B
ON A."FILER_ID_B" = B."FILER_ID"
AND B."FILER_TYPE" = 16 -- code for RECIPIENT COMMITTEE
WHERE "LINK_TYPE" = -12011
GROUP BY 1, 2
ORDER BY "LINK_TYPE";

scenario                       LINK_TYPE  the_count  
-----------------------------  ---------  ---------  
B is a CANDIDATE/OFFICEHOLDER  -12011     20         
B is a RECIPIENT COMMITTEE     -12011     2659       
A is a RECIPIENT COMMITTEE     12011      1201       
A is a CANDIDATE/OFFICEHOLDER  12011      3577