gistfile1.sql

CREATE TABLE players (
    player_id INT AUTO_INCREMENT NOT NULL,
    player_name VARCHAR(255) NOT NULL,
    PRIMARY KEY(player_id)
);

INSERT INTO players (player_name) VALUES
('Sasha'),
('Kris'),
('Max');

CREATE TABLE matches (
    match_id INT AUTO_INCREMENT NOT NULL,
    player_A INT NOT NULL,
    player_B INT NOT NULL,
    winner ENUM ('A', 'B'),
    PRIMARY KEY(match_id),
    FOREIGN KEY (player_A) REFERENCES players (player_id),
    FOREIGN KEY (player_B) REFERENCES players (player_id)
);

INSERT INTO matches (player_A, player_B, winner) VALUES
(2, 1, 'B'),
(3, 2, 'A'),
(1, 3, 'A');

SELECT
    i.player_id,
    i.player_name,
    COUNT(DISTINCT i.match_id) AS matches,
    SUM(i.win) AS wins
FROM
    (SELECT
        p.player_id,
        p.player_name,
        m.match_id,
        IF(
            (p.player_id = m.player_A AND m.winner = 'A') OR 
            (p.player_id = m.player_B AND m.winner = 'B'), 1, 0) AS win
    FROM players p, matches m
    WHERE p.player_id = m.player_A OR p.player_id = m.player_B) AS i
GROUP BY player_id;