SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE players (`player_id` INTEGER, `group_id` INTEGER);
CREATE TABLE matches (`match_id` INTEGER, `first_player` INTEGER, `second_player` INTEGER, `first_score` INTEGER, `second_score` INTEGER);
INSERT INTO players (`player_id`, `group_id`) VALUES
  ('20', '2'),
  ('30', '1'),
  ('40', '3'),
  ('45', '1'),
  ('50', '2'),
  ('65', '1');
INSERT INTO matches (`match_id`, `first_player`, `second_player`, `first_score`, `second_score`) VALUES
  ('1', '30', '45', '10', '12'),
  ('2', '20', '50', '5', '5'),
  ('13', '65', '45', '10', '10'),
  ('5', '30', '65', '3', '15'),
  ('42', '45', '65', '8', '4');
SELECT DISTINCT p.group_id, 
       FIRST_VALUE(p.player_id) OVER (PARTITION BY p.group_id ORDER BY m.score DESC) winner_id
FROM players p
LEFT JOIN (
  SELECT player, SUM(score) score
  FROM (
    SELECT match_id, first_player player, first_score score FROM matches
    UNION ALL
    SELECT match_id, second_player, second_score FROM matches
  ) t
  GROUP BY player
) m ON m.player = p.player_id  


group_id
winner_id


1
45

2
20

3
40