MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Players (
  Player_Name VARCHAR(100),
  Current_ELO INT,
  Player_Status INT
);

INSERT INTO Players VALUES
  ('John', 1, 1), ('Jack', 2, 1), ('Max', 4, 1), ('Tom', 3, 1);
  
CREATE TABLE Replays (
  ReplayID INT,
  ReplayName VARCHAR(100),
  Winner VARCHAR(100),
  Loser VARCHAR(100)
);

INSERT INTO Replays VALUES
  (132, 'Fun Game', 'John', 'Jack'),
  (133, 'Fun Game', 'Max', 'Jack'),
  (134, 'Fun Game', 'Jack', 'Tom');
SELECT Players.Player_Name, ANY_VALUE(Current_ELO) AS Current_ELO, ANY_VALUE(Player_Status) AS Player_Status,
  SUM(CASE WHEN result = 'L' THEN 1 ELSE 0 END) AS Lost_Games, 
  SUM(CASE WHEN result = 'W' THEN 1 ELSE 0 END) AS Won_Games
FROM Players INNER JOIN (
  SELECT 'W' AS result, Winner AS Player_Name FROM Replays
  UNION ALL
  SELECT 'L', Loser FROM Replays
) ReplayResults ON Players.Player_Name = ReplayResults.Player_Name
GROUP BY Players.Player_Name
ORDER BY ANY_VALUE(Current_ELO) DESC;


Player_Name
Current_ELO
Player_Status
Lost_Games
Won_Games


Max
4
1
0
1

Tom
3
1
1
0

Jack
2
1
2
1

John
1
1
0
1


SELECT Players.Player_Name, ANY_VALUE(Current_ELO) AS Current_ELO, ANY_VALUE(Player_Status) AS Player_Status,
  COUNT(DISTINCT rLoser.ReplayID) AS Lost_Games, 
  COUNT(DISTINCT rWinner.ReplayID) AS Won_Games
FROM Players
  LEFT JOIN Replays AS rWinner ON rWinner.Winner = Players.Player_Name
  LEFT JOIN Replays AS rLoser ON rLoser.Loser = Players.Player_Name
GROUP BY Players.Player_Name
ORDER BY ANY_VALUE(Current_ELO) DESC;


Player_Name
Current_ELO
Player_Status
Lost_Games
Won_Games


Max
4
1
0
1

Tom
3
1
1
0

Jack
2
1
2
1

John
1
1
0
1