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)) &lt; 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 *
            FROM dbo.MatchesParticipants mp2
            WHERE mp2.Winner = 0
            ) Losers ON Losers.MatchID = Winners.MatchID
                AND Losers.ParticipantID &lt;&gt; Winners.ParticipantID
)
, WinsLosses AS
(
    SELECT source.Winner
        , source.Loser
        , Wins = COUNT(1)
        , Losses = (SELECT COUNT(1) 
                     FROM source l 
                     WHERE l.Loser = source.Winner 
                         AND l.Winner = source.Loser
                   )
    FROM source
    GROUP BY source.Winner
        , source.Loser
)
SELECT Winner = pw.ParticipantName
    , Loser = pl.ParticipantName
    , WinsLosses.Wins
    , GamesPlayed = WinsLosses.Wins + WinsLosses.Losses
    , WinRatio = CONVERT(decimal(10,5), WinsLosses.Wins) /
                      (WinsLosses.Losses + WinsLosses.Wins)
FROM WinsLosses
        INNER JOIN dbo.Participants pw ON WinsLosses.Winner = pw.ParticipantID
        INNER JOIN dbo.Participants pl ON WinsLosses.Loser = pl.ParticipantID
ORDER BY pw.ParticipantName
    , pl.ParticipantName;


Results:

╔══════════╦═════════════════════╦══════╦═════════════╦════════════════════╗
║ Winner   ║ Loser               ║ Wins ║ GamesPlayed ║ WinRatio           ║
╠══════════╬═════════════════════╬══════╬═════════════╬════════════════════╣
║ addr     ║ column_id           ║ 2651 ║ 5252        ║ 0.5047600913937547 ║
║ addr     ║ csn                 ║ 2535 ║ 5210        ║ 0.4865642994241842 ║
║ addr     ║ depid               ║ 2663 ║ 5294        ║ 0.5030222893842085 ║
║ addr     ║ fileguid            ║ 2592 ║ 5256        ║ 0.4931506849315068 ║
║ addr     ║ grantee             ║ 2604 ║ 5179        ║ 0.5027997682950376 ║
║ addr     ║ internalstatus      ║ 2636 ║ 5224        ║ 0.5045941807044410 ║
║ addr     ║ name                ║ 2662 ║ 5265        ║ 0.5056030389363722 ║
║ addr     ║ name                ║ 2552 ║ 5206        ║ 0.4902036112178255 ║
║ addr     ║ ParticipantName     ║ 2580 ║ 5248        ║ 0.4916158536585365 ║
║ addr     ║ pgfirst             ║ 2593 ║ 5252        ║ 0.4937166793602437 ║
║ addr     ║ product             ║ 2599 ║ 5280        ║ 0.4922348484848484 ║
║ addr     ║ protocol            ║ 2635 ║ 5238        ║ 0.5030546009927453 ║
║ addr     ║ service_id          ║ 2610 ║ 5216        ║ 0.5003834355828220 ║
║ affinity ║ binary_message_body ║ 2605 ║ 5222        ║ 0.4988510149368058 ║
║ affinity ║ dbid                ║ 2568 ║ 5208        ║ 0.4930875576036866 ║
║ affinity ║ diffbaseseclsn      ║ 2554 ║ 5199        ║ 0.4912483169840353 ║
║ affinity ║ encoding_type       ║ 2652 ║ 5259        ║ 0.5042783799201369 ║
╚══════════╩═════════════════════╩══════╩═════════════╩════════════════════╝


The plan:



The Source CTE joins the "winners" and the "losers" for each match.  The WinsLosses CTE provides the total for Wins and Losses.  Finally, the main query shows each participant name, their opponent's name, the number of Wins, Games Played, and of course, the Win Ratio.  On my system, this generates 9,744 rows in about 18 seconds - the results of this query could be pre-computed every 15 minutes for display to the end user.

To get that speed out of this query, I created these two covering indexes:

CREATE NONCLUSTERED INDEX MatchesParticipants_ix01
ON dbo.MatchesParticipants(Winner, MatchID)
INCLUDE (ParticipantID);

CREATE NONCLUSTERED INDEX MatchesParticipants_ix02
ON dbo.MatchesParticipants (ParticipantID, Winner)
INCLUDE (MatchID);


If you're interested in obtaining the stats for any specific given combination of players, this query does the trick:

DECLARE @ParticipantID1 int;
DECLARE @ParticipantID2 int;

SET @ParticipantID1 = 216;
SET @ParticipantID2 = 566;

;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 *
            FROM dbo.MatchesParticipants mp2
            WHERE mp2.Winner = 0
            ) Losers ON Losers.MatchID = Winners.MatchID
                AND Losers.ParticipantID &lt;&gt; Winners.ParticipantID
)
, WinsLosses AS
(
    SELECT source.Winner
        , source.Loser
        , Wins = COUNT(1)
        , Losses = (SELECT COUNT(1) FROM source l WHERE l.Loser = source.Winner AND l.Winner = source.Loser)
    FROM source
    GROUP BY source.Winner
        , source.Loser
)
SELECT Winner 
    , Loser 
    , WinsLosses.Wins
    , GamesPlayed = WinsLosses.Wins + WinsLosses.Losses
    , WinRatio = CONVERT(decimal(10,2), WinsLosses.Wins) / (WinsLosses.Losses + WinsLosses.Wins)
FROM WinsLosses
WHERE Winner = @ParticipantID1
    AND Loser = @ParticipantID2
ORDER BY Winner
    , Loser;


The results of the query:

╔════════╦═══════╦══════╦═════════════╦═════════════════╗
║ Winner ║ Loser ║ Wins ║ GamesPlayed ║ WinRatio        ║
╠════════╬═══════╬══════╬═════════════╬═════════════════╣
║ 216    ║ 566   ║ 2637 ║ 5233        ║ 0.5039174469711 ║
╚════════╩═══════╩══════╩═════════════╩═════════════════╝


Results are returned immediately.  This is the actual execution plan: