MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table players (

player_id integer not null unique,

group_id integer not null

);

create table matches (

match_id integer not null unique,

first_player integer not null,

second_player integer not null,

first_score integer not null,

second_score integer not null

);

insert into players values(20, 2);

insert into players values(30, 1);

insert into players values(40, 3);

insert into players values(45, 1);

insert into players values(50, 2);

insert into players values(65, 1);

insert into matches values(1, 30, 45, 10, 12);

insert into matches values(2, 20, 50, 5, 5);

insert into matches values(13, 65, 45, 10, 10);

insert into matches values(5, 30, 65, 3, 15);

insert into matches values(42, 45, 65, 8, 4);

WITH match_records AS (

SELECT match_id,first_player players, first_score scores FROM matches UNION ALL

SELECT match_id,second_player, second_score FROM matches

)

SELECT group_id, player_id

FROM

(SELECT group_id, player_id, players, SUM(scores) ts,

ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY SUM(scores) DESC) pos

FROM players p LEFT JOIN match_records mr ON mr.players=p.player_id

GROUP BY group_id, player_id, players) fp

WHERE pos=1

ORDER BY group_id;