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.

Here is the expected output.

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