How to use CTE to Compare Values

I need to return a 0 or 1 dependent on previous records. Example table:

DECLARE @x TABLE(ProductID INT, Failed bit, SampleDate date, LevelCode int);
INSERT @x VALUES
(101, 0, '20151201', 1),    
(101, 1, '20151205', 2),    
(101, 0, '20151206', 3),
(101, 1, '20151208', 2), 
(102, 1, '20151202', 1),
(102, 0, '20151204', 2),    
(102, 0, '20151205', 3),
(103, 0, '20160101', 1),
(103, 1, '20160102', 2),
(103, 0, '20160103', 2),
(104, 0, '20160101', 1),
(104, 0, '20160102', 2),
(104, 0, '20160103', 3);

The only thing we care about on the last record is the LevelCode (i.e. last record of each ProductID). Whether the last record passed/failed doesnt matter. We then look at all other records for that ProductID (so all records before the last record) and if there was a failure with the same LevelCode as the last record we set IsLastRunSameLevelAsPreviousRun to 1 else 0:

ProductID     IsLastRunSameLevelAsPreviousRun
 101           1
 102           0
 103           1
 104           0

If there are no failures for a ProductID the IsLastRunSameLevelAsPreviousRun should return 0.

Any help or tips are very much appreciate.

Using LAG you can achieve your goal without any joins:

WITH cte AS
  (
    SELECT
      ProductID,
      SampleDate,
      MaxSampleDate = MAX(SampleDate) OVER (PARTITION BY ProductID),
      PrevFailed    = LAG(Failed, 1, 0) OVER (PARTITION BY ProductID, LevelCode
                                              ORDER BY SampleDate)
    FROM
      @x
  )
SELECT
  ProductID,
  IsLastRunSameLevelAsPreviousRun = PrevFailed
FROM
  cte
WHERE
  SampleDate = MaxSampleDate
;

The cte obtains the previous Failed state for the same LevelCode for each row within each ProductID group, returning 0 when there is no matching row. It also calculates the last date in each group to use it later to determine the last row in the group.

This is what it returns:

ProductID  SampleDate  MaxSampleDate  PrevFailed
---------  ----------  -------------  ----------
101        2015-12-01  2015-12-08     0
101        2015-12-05  2015-12-08     0
101        2015-12-08  2015-12-08     1
101        2015-12-06  2015-12-08     0
102        2015-12-02  2015-12-05     0
102        2015-12-04  2015-12-05     0
102        2015-12-05  2015-12-05     0
103        2016-01-01  2016-01-03     0
103        2016-01-02  2016-01-03     0
103        2016-01-03  2016-01-03     1
104        2016-01-01  2016-01-03     0
104        2016-01-02  2016-01-03     0
104        2016-01-03  2016-01-03     0

The main SELECT essentially just takes the last row of each group using the SampleDate = MaxSampleDate filter, pulling only ProductID and PrevFailed and also renaming the latter to IsLastRunSameLevelAsPreviousRun, so that the final output becomes what you want:

ProductID  IsLastRunSameLevelAsPreviousRun
---------  -------------------------------
101        1
102        0
103        1
104        0

StackExchange Database Administrators Q#129358, answer score: 6