SELECT gp.id as global_participant_id, main.*, cma.band, cma.gender, ap.pronoun, genres.genres
    FROM
        (
        SELECT SPOTIFY_ARTIST_ID, CM_ARTIST, POPULARITY_LATEST, ARTIST_NAME, DESCRIPTION
        FROM CHARTMETRIC.RAW_DATA.SPOTIFY_ARTIST
        ) main LEFT JOIN
        (
        select CM_ARTIST, LISTAGG(GENRE_NAME, '|') as GENRES FROM (
        SELECT at.cm_artist, at.LINK_TYPE, mg.NAME AS genre_name FROM
        CHARTMETRIC.RAW_DATA.L_CM_ARTIST_TAG at left JOIN CHARTMETRIC.RAW_DATA.CM_TAG mg
        ON at.CM_TAG = mg.ID
        WHERE at.LINK_TYPE IN ('cm_artist_genre', 'cm_artist_sub_genre')
        AND NOT at.HIDDEN
        ) sub GROUP BY CM_ARTIST
        ) genres
    ON main.CM_ARTIST=genres.CM_ARTIST
        INNER JOIN facts.prod.global_participant gp ON main.spotify_artist_id = gp.spotify_id
        INNER JOIN CHARTMETRIC.RAW_DATA.CM_ARTIST cma ON main.CM_ARTIST = cma.ID
        LEFT JOIN CHARTMETRIC.raw_data.l_cm_artist_pronoun ap ON main.CM_ARTIST = ap.CM_ARTIST
    WHERE NOT cma.is_duplicate