Hello, here is a puzzle that makes perfect use of the FIRST_VALUE, LAST_VALUE, LAG, and the MIN functions.
For the following dataset, you need to find the difference between each player’s first score against the current score, as well as their last score against the current score. If their score improved from their previous score, mark the record as improved. If each and every iteration improved, mark the player as overall improved.
|Attempt ID||Player ID||Score|
Here is the expected output.
|Attempt ID||Player ID||Score||Diff First||Diff Last||Is Previous Score Lower||Is Overall Improved|
Here is the SQL to generate the answer.
DROP TABLE IF EXISTS #PlayerScores; GO CREATE TABLE #PlayerScores ( AttemptID INTEGER, PlayerID INTEGER, Score INTEGER, PRIMARY KEY (AttemptID, PlayerID) ); GO INSERT INTO #PlayerScores (AttemptID, PlayerID, Score) VALUES (1,1001,2),(2,1001,7),(3,1001,8),(1,2002,6),(2,2002,9),(3,2002,7); GO WITH cte_FirstLastValues AS ( SELECT * ,FIRST_VALUE(Score) OVER (PARTITION BY PlayerID ORDER BY AttemptID) AS FirstValue ,LAST_VALUE(Score) OVER (PARTITION BY PlayerID ORDER BY AttemptID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LastValue ,LAG(Score,1,99999999) OVER (PARTITION BY PlayerID ORDER BY AttemptID) AS LagScore ,CASE WHEN Score - LAG(Score,1,0) OVER (PARTITION BY PlayerID ORDER BY AttemptID) > 0 THEN 1 ELSE 0 END AS IsImproved FROM #PlayerScores ) SELECT AttemptID ,PlayerID ,Score ,Score - FirstValue AS Difference_First ,Score - LastValue AS Difference_Last ,IsImproved AS IsPreviousScoreLower ,MIN(IsImproved) OVER (Partition BY PlayerID) AS IsOverallImproved FROM cte_FirstLastValues; GO