Create distinct candidate committees links

create_candidate_committees.sql

CREATE TABLE calaccess_processed_candidate_committees AS
SELECT   
        @link_type as link_group_id,
        lu."CODE_DESC" AS link_type_description,
        cand_filer_id,
        committee_filer_id,
        MIN(session) AS first_session,
        MAX(session) AS last_session,
        MIN(effective_date) AS first_effective_date,
        MAX(effective_date) AS last_effective_date,
        MIN(termination_date) AS first_termination_date,
        MAX(termination_date) AS last_termination_date
FROM (
        -- select all LINK records where FILER_A was ever a candidate
        SELECT 
                links."FILER_ID_A" AS cand_filer_id,
                links."FILER_ID_B" AS committee_filer_id,
                "LINK_TYPE" AS link_type,
                "SESSION_ID" AS session,
                "ACTIVE_FLG" AS active,
                "EFFECT_DT" AS effective_date,
                "TERMINATION_DT" AS termination_date,
                links.id
        FROM "FILER_LINKS_CD" links
        JOIN (
                SELECT DISTINCT "FILER_ID"
                FROM "FILER_TO_FILER_TYPE_CD"
                WHERE "FILER_TYPE" = 8
        ) cands
        ON cands."FILER_ID" = links."FILER_ID_A"
        JOIN (
                SELECT DISTINCT "FILER_ID"
                FROM "FILER_TO_FILER_TYPE_CD"
                WHERE "FILER_TYPE" = 16
        ) comms
        ON comms."FILER_ID" = links."FILER_ID_B"
        -- union with all LINK records where FILER B was ever a candidate
        UNION ALL
        SELECT 
                links."FILER_ID_B" AS cand_filer_id,
                links."FILER_ID_A" AS committee_filer_id,
                "LINK_TYPE" as link_type,
                "SESSION_ID" AS session,
                "ACTIVE_FLG" AS active,
                "EFFECT_DT" AS effective_date,
                "TERMINATION_DT" AS termination_date,
                links.id
        FROM "FILER_LINKS_CD" links
        JOIN (
                SELECT DISTINCT "FILER_ID"
                FROM "FILER_TO_FILER_TYPE_CD"
                WHERE "FILER_TYPE" = 8
        ) cands
        ON cands."FILER_ID" = links."FILER_ID_B"
        JOIN (
                SELECT DISTINCT "FILER_ID"
                FROM "FILER_TO_FILER_TYPE_CD"
                WHERE "FILER_TYPE" = 16
        ) comms
        ON comms."FILER_ID" = links."FILER_ID_A"
) as all_links
JOIN "LOOKUP_CODES_CD" lu
ON @link_type = lu."CODE_ID"
AND lu."CODE_TYPE" = 12000
GROUP BY 1, 2, 3, 4;