CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-design
Simon Loh
Simon Loh

Reputation: 335

Cumulative Game Score SQL

I have developed a game recently and the database is running on MSSQL.

Here is my database structure

Table : Player

PlayerID uniqueIdentifier (PK)
PlayerName nvarchar

Table : GameResult

ID bigint (PK - Auto Increment)
PlayerID uniqueIdentifier (FK)
DateCreated Datetime
Score int
TimeTaken bigint
PuzzleID int

I have done an SQL listing Top 50 players that sort by highest score (DESC) and timetaken (ASC)

WITH ResultSet (PlayerID, Score, TimeTaken) AS(
  SELECT DISTINCT(A.[PlayerID]), MAX(A.[Score]),MIN(A.[TimeTaken])
  FROM GameResult A
  WHERE A.[puzzleID] = @PuzzleID
  GROUP BY A.[PlayerID])
SELECT TOP 50 RSP.[PlayerID], RSP.[PlayerName], RSA.[Score], RSA.[TimeTaken]
FROM ResultSet RSA
INNER JOIN Player RSP WITH(NOLOCK) ON RSA.PlayerID = RSP.PlayerID
ORDER By RSA.[Score] DESC, RSA.[timetaken] ASC

However above is applicable for just 1 puzzle.

Question

1) I need to modify the SQL to do a cumulative rank of 3 puzzle ID. For example, Puzzle 1, 2, 3 and it should be sort by highest sum score (DESC), and sum timetaken (ASC)

2) I also need an overall score population for all the possible 1 to 7 puzzle.

3) Each player only allowed to appear on the list once. First played and first to get highest score will be rank 1st.

I tried using CTE with UNION but the SQL statement doesn't work.

I hope gurus here can help me out on this. Much appreciated.

UPDATED WITH NEW SQL

Sql below allowed me to get the result for each puzzle id. I'm not sure if it is 100% but I believe it is correct.

;with ResultSet (PlayerID, maxScore, minTime, playedDate) 
AS
(
  SELECT TOP 50 PlayerID, MAX(score) as maxScore, MIN(timetaken) as minTime, MIN(datecreated) as playedDate
    FROM gameresult
    WHERE puzzleID = @PuzzleID
    GROUP BY PlayerID
    ORDER BY maxScore desc, minTime asc, playedDate asc
)
SELECT RSP.[PlayerID], RSP.[PlayerName], RSA.maxScore, RSA.minTime, RSA.PlayedDate
FROM ResultSet RSA
INNER JOIN Player RSP WITH(NOLOCK) 
    ON RSA.PlayerID = RSP.PlayerID
ORDER BY 
    maxScore DESC, 
    minTime ASC,
    playedDate ASC

Upvotes: 0

Views: 605

Answers (1)

Adam Haines
Adam Haines

Reputation: 920

I would first like to point out that I do not believe your original query is correct. If you are looking for the best player for a particular puzzle, would that be the combination of the highest score plus the best time for that puzzle? If yes, using max and min does not guarantee that the max and min come from the same game (or row), which I believe should be a requirement. Instead you should have first determined the best game per player by using a row number windowing function. You can then do the top 50 sort off of that data.

The cumulative metrics should be easier to calculate because you only have to aggregate the sum of their score and the sum of their time and then sort, which means the new query should most likely look something like this:

;with ResultSet (PlayerID, Score, TimeTaken) 
AS
(
SELECT TOP 50 
    A.[PlayerID], 
    SUM(A.[Score]),
    SUM(A.[TimeTaken])
FROM GameResult  A
WHERE 
    A.[puzzleID] in(1,2,3)
GROUP BY 
    A.PlayerID
ORDER BY 
    SUM(A.[Score]) DESC,
    SUM(A.[TimeTaken]) ASC
)
SELECT RSP.[PlayerID], RSP.[PlayerName], RSA.[Score], RSA.[TimeTaken]
FROM ResultSet RSA
INNER JOIN Player RSP WITH(NOLOCK) 
    ON RSA.PlayerID = RSP.PlayerID
ORDER BY 
    Score DESC, 
    TimeTaken ASC

UPDATE:

Based on the new criteria, you will have to do something like this.

;WITH ResultSet (PlayerID, PuzzleId, Score, TimeTaken, seq) 
AS
(
SELECT
    A.[PlayerID], 
    A.PuzzleID,
    A.[Score],
    A.[TimeTaken],
    seq = ROW_NUMBER() over(PARTITION BY PlayerID, PuzzleId ORDER BY Score DESC)
FROM GameResult A
WHERE 
    A.[puzzleID] in(1,2,3)
)
SELECT TOP 50
    RSP.[PlayerID], 
    RSP.[PlayerName], 
    Score = SUM(RSA.[Score]), --total score
    TimeTaken = SUM(RSA.[TimeTaken]) --total time taken
FROM ResultSet RSA
INNER JOIN Player RSP 
    ON RSA.PlayerID = RSP.PlayerID
WHERE
    --this is used to filter the top score for each puzzle per player
    seq = 1
GROUP BY
    RSP.[PlayerID], 
    RSP.[PlayerName]
ORDER BY
    SUM(RSA.Score) DESC,
    SUM(RSA.TimeTaken) ASC

Upvotes: 3

Related Questions