Combos of filer a and filer b types

filer_a_and_b_type_combos.sql

SELECT 
        a_types."DESCRIPTION" AS filer_a_type,
        b_types."DESCRIPTION" AS filer_b_type,
        COUNT(DISTINCT links.id)
FROM "FILER_LINKS_CD" links
JOIN (
        SELECT "FILER_ID", "DESCRIPTION"
        FROM "FILER_TO_FILER_TYPE_CD" f2ft
        JOIN "FILER_TYPES_CD" ft
        ON f2ft."FILER_TYPE" = ft."FILER_TYPE"
) AS a_types
ON links."FILER_ID_A" = a_types."FILER_ID"
JOIN (
        SELECT "FILER_ID", "DESCRIPTION"
        FROM "FILER_TO_FILER_TYPE_CD" f2ft
        JOIN "FILER_TYPES_CD" ft
        ON f2ft."FILER_TYPE" = ft."FILER_TYPE"
) AS b_types
ON links."FILER_ID_B" = b_types."FILER_ID"
WHERE @links."LINK_TYPE" = 12011
GROUP BY 1, 2
ORDER BY COUNT(DISTINCT links.id) DESC;

filer_a_type                                   filer_b_type                   count  
---------------------------------------------  -----------------------------  -----  
CANDIDATE/OFFICEHOLDER                         RECIPIENT COMMITTEE            8249   
RECIPIENT COMMITTEE                            CANDIDATE/OFFICEHOLDER         1167   
RECIPIENT COMMITTEE                            TREASURER/RESPONSIBLE OFFICER  44     
RECIPIENT COMMITTEE                            RECIPIENT COMMITTEE            44     
CANDIDATE/OFFICEHOLDER                         CANDIDATE/OFFICEHOLDER         8      
TREASURER/RESPONSIBLE OFFICER                  RECIPIENT COMMITTEE            3      
PROPONENT                                      RECIPIENT COMMITTEE            2      
CANDIDATE/OFFICEHOLDER                         TREASURER/RESPONSIBLE OFFICER  2      
MAJOR DONOR/INDEPENDENT EXPENDITURE COMMITTEE  RECIPIENT COMMITTEE            1      
CANDIDATE/OFFICEHOLDER                         SLATE MAILER ORGANIZATIONS     1