Retrieve all champions in a game and the winning team

get_teams.sql

copy (
    with teams as (
        select
            team_id,
            max(case when role = 'TOP' then champion_id else 0 end) as top,
            max(case when role = 'JUNGLE' then champion_id else 0 end) as jungle,
            max(case when role = 'MIDDLE' then champion_id else 0 end) as mid,
            max(case when role = 'DUO_SUPPORT' then champion_id else 0 end) as support,
            max(case when role = 'DUO_CARRY' then champion_id else 0 end) as adc
            -- string_agg(
            --     text(champion_id),
            --     ','
            --     ORDER BY array_position(array['TOP','JUNGLE','MIDDLE','DUO_SUPPORT','DUO_CARRY'], 'role')
            -- ) as champions
        from summoner_performances
        group by team_id
    )
    select
        T1.top,
        T1.jungle,
        T1.mid,
        T1.support,
        T1.adc,
        T2.top,
        T2.jungle,
        T2.mid,
        T2.support,
        T2.adc,
        case when winning_team_id = team1_id then 0 else 1 end
    from matches as M
    inner join teams as T1 on
        T1.team_id = M.team1_id
    inner join teams as T2 on
        T2.team_id = M.team2_id
    order by M.created_at desc
) to STDOUT with csv delimiter ',';