Binaryworld - Finding average of top 3 scores for each player. ... [ SQL Server -> T-SQL ]

Solution for determining the top 3 scores for each player in a game. This script demonstrates several SQL techniques both ANSI & T-SQL specific.

Click here to copy the following block

CREATE TABLE #Scores (
    player int NOT NULL,
    game_date datetime NOT NULL,
    score integer NOT NULL,
    CONSTRAINT PK_Scores_Player_Game PRIMARY KEY ( player, game_date )
);

INSERT INTO #Scores VALUES(1,'20000101',50);
INSERT INTO #Scores VALUES(1,'20000102',51);
INSERT INTO #Scores VALUES(1,'20000103',52);
INSERT INTO #Scores VALUES(1,'20000104',53);
INSERT INTO #Scores VALUES(1,'20000105',54);
INSERT INTO #Scores VALUES(1,'20000106',55);
INSERT INTO #Scores VALUES(2,'20000101',56);
INSERT INTO #Scores VALUES(2,'20000102',57);
INSERT INTO #Scores VALUES(2,'20000103',58);
INSERT INTO #Scores VALUES(2,'20000104',59);
INSERT INTO #Scores VALUES(2,'20000105',60);
INSERT INTO #Scores VALUES(2,'20000106',61);
INSERT INTO #Scores VALUES(3,'20000111',71);
GO
SELECT * FROM #Scores
ORDER BY player , game_date DESC;
/*
player game_date        score
------ ----------------------- -----
   1 2000-01-06 00:00:00.000  55
   1 2000-01-05 00:00:00.000  54
   1 2000-01-04 00:00:00.000  53
   1 2000-01-03 00:00:00.000  52
   1 2000-01-02 00:00:00.000  51
   1 2000-01-01 00:00:00.000  50
   2 2000-01-06 00:00:00.000  61
   2 2000-01-05 00:00:00.000  60
   2 2000-01-04 00:00:00.000  59
   2 2000-01-03 00:00:00.000  58
   2 2000-01-02 00:00:00.000  57
   2 2000-01-01 00:00:00.000  56
   3 2000-01-11 00:00:00.000  71
*/
GO

/*
    #1. ANSI SQL way using a correlated sub-query
*/
SELECT s1.Player , AVG( s1.Score ) AS Avg_Score
FROM #Scores AS s1
WHERE ( SELECT COUNT( * ) FROM #Scores AS s2
    WHERE s1.Player = s2.Player And s1.game_date <= s2.game_date ) <= 3
GROUP BY s1.Player
ORDER BY s1.Player;

/*
    #2. ANSI SQL way flattened as a JOIN. But may be slower than #1 & #3.
*/
SELECT s.Player , AVG( s.Score ) AS Avg_Score
FROM (
    SELECT s1.Player , s1.Score
    FROM #Scores AS s1 JOIN #Scores AS s2
    ON s1.Player = s2.Player
    WHERE s1.game_date <= s2.game_date
    GROUP BY s1.Player , s1.Score
    HAVING COUNT( * ) <= 3
) AS s ( Player , Score )
GROUP BY s.Player
ORDER BY s.Player;

/*
    #3. T-SQL method using TOP operator available in SQL70/2000.
*/
SELECT s1.Player , AVG( s1.Score ) AS Avg_Score
FROM #Scores AS s1
WHERE s1.game_date IN ( SELECT TOP 3 s2.game_date FROM #Scores AS s2
            WHERE s1.Player = s2.Player
            ORDER BY s2.game_date DESC )
GROUP BY s1.Player
ORDER BY s1.Player;
/*
Player   Avg_Score
----------- -----------
     1     54
     2     60
     3     71
*/
GO
DROP TABLE #Scores;
GO