Opponent Winrate Over another opponent sql

I am trying to find the most efficient way to build a counter warehouse.

shortened the tables just to needed info for question

Match Table

MatchId, version, type

MatchParticipants

matchId, playerid, characterid, teamid (only team1 or team2), winner (1 or 0)

10 of these rows per match

Characters

characterid, name

I considered doing a cross join of characters on characters to give me all the possible of opponents for each character. Then I would have do a massive sub-query to look into the matches table where the id's were on opposite teams.

Any ideas essentially what I see the warehouse table looking like.

character1.characterid, character1.name,
character2.characterid, character2.name, 
winrate of character1 over character2

I've mocked up the minimal tables you mentioned in your question.

First, we'll run this in tempdb and cleanup anything that might get in the way:

USE tempdb;
IF OBJECT_ID(N'dbo.MatchesParticipants', N'U') IS NOT NULL
DROP TABLE dbo.MatchesParticipants;
IF OBJECT_ID(N'dbo.Matches', N'U') IS NOT NULL
DROP TABLE dbo.Matches;
IF OBJECT_ID(N'dbo.Participants', N'U') IS NOT NULL
DROP TABLE dbo.Participants;
GO

Next, we create three tables. Matches, Participants, and MatchesParticipants:

CREATE TABLE dbo.Matches
(
    MatchID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Matches
        PRIMARY KEY CLUSTERED
    , VersionNum int NOT NULL
    , MatchType int NOT NULL
);

CREATE TABLE dbo.Participants
(
    ParticipantID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Participants
        PRIMARY KEY CLUSTERED
    , ParticipantName varchar(100) NOT NULL
);

CREATE TABLE dbo.MatchesParticipants
(
    MatchID int NOT NULL
        FOREIGN KEY REFERENCES dbo.Matches(MatchID)
    , ParticipantID int NOT NULL
        FOREIGN KEY REFERENCES dbo.Participants(ParticipantID)
    , TeamID int NOT NULL
    , Winner bit NOT NULL
    , CONSTRAINT PK_MatchesParticipants 
        PRIMARY KEY CLUSTERED (MatchID, TeamID, ParticipantID)
);

Here we create some "matches"; on my system this inserted over 522,000 rows:

INSERT INTO dbo.Matches (VersionNum, MatchType)
SELECT c1.object_id % 5
    , c2.object_id % 5
FROM sys.columns c1
    CROSS JOIN sys.columns c2;

And this inserts the "Participants"; on my system I got over 700:

INSERT INTO dbo.Participants (ParticipantName)
SELECT c1.name
FROM sys.columns c1;

I used this to create rows in "MatchParticipants". The code uses a CTE to cross join Matches and Participants in such a way that only 1/50th of the rows in a "real" cross join are inserted. This resulted in 7,558,671 rows. The CASE WHEN ASCII(CRYPT_GEN_RANDOM(1)) ... piece randomly selects each participant to be either on Team 0 or Team 1, with Team 1 always being the "winner":

;WITH src AS 
(
SELECT m.MatchID
    , p.ParticipantID
    , val = CASE WHEN ASCII(CRYPT_GEN_RANDOM(1)) < 127 
        THEN 1 /* randomly assigned winning team */
        ELSE 0 
        END 
FROM dbo.Matches m
    INNER JOIN dbo.Participants p 
        ON m.MatchID % 50 = p.ParticipantID % 50 /* 1/50th of a CROSS JOIN  */
)
INSERT INTO dbo.MatchesParticipants (MatchID, ParticipantID, TeamID, Winner)
SELECT src.MatchID
    , src.ParticipantID
    , src.val
    , src.val
FROM src;

Some sample data from the MatchesParticipants table:

╔═════════╦═══════════════╦════════╦════════╗
║ MatchID ║ ParticipantID ║ TeamID ║ Winner ║
╠═════════╬═══════════════╬════════╬════════╣
║ 739 ║ 39 ║ 0 ║ 0 ║
║ 739 ║ 89 ║ 0 ║ 0 ║
║ 739 ║ 139 ║ 0 ║ 0 ║
║ 739 ║ 189 ║ 0 ║ 0 ║
║ 739 ║ 239 ║ 0 ║ 0 ║
║ 739 ║ 289 ║ 0 ║ 0 ║
║ 739 ║ 339 ║ 0 ║ 0 ║
║ 739 ║ 389 ║ 0 ║ 0 ║
║ 739 ║ 439 ║ 1 ║ 1 ║
║ 739 ║ 489 ║ 0 ║ 0 ║
║ 739 ║ 539 ║ 1 ║ 1 ║
║ 739 ║ 589 ║ 0 ║ 0 ║
║ 739 ║ 639 ║ 0 ║ 0 ║
║ 739 ║ 689 ║ 0 ║ 0 ║
║ 740 ║ 40 ║ 1 ║ 1 ║
║ 740 ║ 90 ║ 0 ║ 0 ║
║ 740 ║ 140 ║ 0 ║ 0 ║
║ 740 ║ 190 ║ 0 ║ 0 ║
║ 740 ║ 240 ║ 1 ║ 1 ║
║ 740 ║ 290 ║ 1 ║ 1 ║
║ 740 ║ 340 ║ 1 ║ 1 ║
║ 740 ║ 390 ║ 1 ║ 1 ║
║ 740 ║ 440 ║ 0 ║ 0 ║
║ 740 ║ 490 ║ 1 ║ 1 ║
║ 740 ║ 540 ║ 1 ║ 1 ║
║ 740 ║ 590 ║ 1 ║ 1 ║
║ 740 ║ 640 ║ 1 ║ 1 ║
║ 740 ║ 690 ║ 0 ║ 0 ║
╚═════════╩═══════════════╩════════╩════════╝

This shows the row counts for each table:

SELECT ParticipantCount = (SELECT COUNT(1) FROM dbo.Participants)
    , MatchCount = (SELECT COUNT(1) FROM dbo.Matches)
    , MatchParticipantCount = (SELECT COUNT(1) FROM dbo.MatchesParticipants)

╔══════════════════╦════════════╦═══════════════════════╗
║ ParticipantCount ║ MatchCount ║ MatchParticipantCount ║
╠══════════════════╬════════════╬═══════════════════════╣
║ 723 ║ 522729 ║ 7558671 ║
╚══════════════════╩════════════╩═══════════════════════╝

To get the entire list of stats for each pair of players, I used this query:

```
;WITH source AS
(
SELECT Winner = Winners.ParticipantID
, Loser = Losers.ParticipantID
, MatchID = Winners.MatchID
FROM (
SELECT *
FROM dbo.MatchesParticipants mp1
WHERE mp1.Winner = 1
) Winners
INNER JOIN (
SELECT *
F

StackExchange Database Administrators Q#117332, answer score: 2