CREATE VIEW v_matches_won_per_player AS
    SELECT t_players.id, t_players.name,
    COUNT(t_matches.id_winner) AS total_matches_won, t_matches.id_tournament
    FROM t_players
    JOIN t_matches
    ON t_players.id = t_matches.id_winner
    GROUP BY t_players.id, t_matches.id_tournament
    ORDER BY total_matches_won DESC;


CREATE VIEW v_matches_lost_per_player AS
    SELECT t_players.id, t_players.name,
    COUNT(t_matches.id_loser) AS total_matches_lost, t_matches.id_tournament
    FROM t_players JOIN t_matches
    ON t_players.id = t_matches.id_loser
    GROUP BY t_players.id, t_matches.id_tournament
    ORDER BY total_matches_lost DESC;


CREATE VIEW v_total_stats AS
    SELECT v_matches_won_per_player.id, v_matches_won_per_player.name,
           v_matches_won_per_player.total_matches_won AS total_matches_won,
           v_matches_lost_per_player.total_matches_lost AS total_matches_lost,
           v_matches_won_per_player.total_matches_won + v_matches_lost_per_player.total_matches_lost AS total_matches_played
    FROM v_matches_won_per_player, v_matches_lost_per_player
    WHERE v_matches_won_per_player.id = v_matches_lost_per_player.id;