First and Last Values

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 IDPlayer IDScore
110012
210017
310018
420026
520029
620027

Here is the expected output.

Attempt IDPlayer IDScoreDiff FirstDiff LastIs Previous Score LowerIs Overall Improved
1100120-611
2100175-111
3100186011
4200260-110
5200293210
6200271000

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

Leave a Reply