"""Create list of participants """

SELECT_DISTINCT_PARTICIPANTS = """
    SELECT stg_blk.ORCHLABELID,
        sa.SUBACCOUNT_ID,
        pp.PARTICIPANT_NAME,
        pp.APPLE_ID,
        pp.SPOTIFY_URI
    FROM {source_db}.{source_schema}.{source_table} stg_blk
    INNER JOIN
        {participant_db}.{participant_schema}.{release_participant_table} pp
        ON stg_blk.DIGITAL_UPC = pp.UPC
    LEFT OUTER JOIN {reporting_db}.{reporting_schema}.{reporting_table} sa
        ON stg_blk.SUBACCOUNT_NAME = sa.SUBACCOUNT_NAME
        AND sa.vendor_id = stg_blk.orchlabelid

GROUP BY 1,2,3,4,5
UNION
    SELECT stg_blk.ORCHLABELID,
        sa.SUBACCOUNT_ID,
        tp.PARTICIPANT_NAME,
        tp.APPLE_ID,
        tp.SPOTIFY_URI
    FROM {source_db}.{source_schema}.{source_table} stg_blk
    INNER JOIN
        {participant_db}.{participant_schema}.{track_participant_table} tp
        ON stg_blk.DIGITAL_UPC = tp.UPC
    LEFT OUTER JOIN {reporting_db}.{reporting_schema}.{reporting_table} sa
        ON stg_blk.SUBACCOUNT_NAME = sa.SUBACCOUNT_NAME
        AND sa.vendor_id = stg_blk.orchlabelid
GROUP BY 1,2,3,4,5
"""