Mathematically compare multiple 3 rows sets of table and record result in new column I need to isolate 3 row groups in my overall query and perform the same CASE statement on each group and return a result in the final output

I believe i may have to use a CTE (which i have not done before)
but im not sure if this is the right thing to do

I will reuse a graphic from a previous question to illustrate.
The maths is simple, i just am unsure as to how to compare the 3 rows distinctly from the main table.

SQL Server 2012
There are probably more efficient ways to do this, but here's my stab at it.
For each group of 3, I'm basically capturing the value of Result for Row1, Row2 and Row3 - sort of like a poor-man's PIVOT.  I then use a CASE expression to evaluate the differences between Row1, Row2 and Row3 to generate the 'Up' or 'Down' values and concatenate those values for each row

SET NOCOUNT ON
declare @Table table (Col1 Char(1), Col2 Char(2), Col3 Char(2), [Year] int, [Year+n] int, Result int)
insert into @Table 
    (Col1, Col2, Col3, [Year], [Year+n], Result) 
    values 
        ('A', 'UK', '1Z', 2015, 2, 30),
        ('A', 'UK', '1Z', 2015, 1, 20), 
        ('A', 'UK', '1Z', 2015, 0, 10), 
        ('A', 'UK', '1Y', 2015, 2, 40),
        ('A', 'UK', '1Y', 2015, 1, 35),
        ('A', 'UK', '1Y', 2015, 0, 40)
;
WITH _CTE as 
(
SELECT Col1
    ,Col2
    ,Col3
    ,[YEAR]
    ,[Year+n]
    ,Result
    ,CASE 
        WHEN 
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3 
                                ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 1 THEN
                Result
        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3 
                            ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 2 THEN
                LAG(Result,1) OVER (PARTITION BY Col1, Col2, Col3 
                            ORDER by Col1, Col2, Col3, [Year], [Year+n] ) 

        WHEN 
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3 
                                ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 3 THEN
                LAG(Result,2) OVER (PARTITION BY Col1, Col2, Col3 
                            ORDER by Col1, Col2, Col3, [Year], [Year+n] ) 
        END AS Row1Result
    ,CASE 
        WHEN 
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3 
                                ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 1 THEN
                LEAD(Result,1) OVER (PARTITION BY Col1, Col2, Col3 
                            ORDER by Col1, Col2, Col3, [Year], [Year+n] ) 
        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3 
                            ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 2 THEN
                Result
        WHEN 
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3 
                                ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 3 THEN
                LAG(Result,1) OVER (PARTITION BY Col1, Col2, Col3 
                            ORDER by Col1, Col2, Col3, [Year], [Year+n] ) 
                            END AS Row2Result
    ,CASE 
        WHEN 
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3 
                                ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 1 THEN
                LEAD(Result,2) OVER (PARTITION BY Col1, Col2, Col3 
                            ORDER by Col1, Col2, Col3, [Year], [Year+n] ) 
        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3 
                            ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 2 THEN
                LEAD(Result,1) OVER (PARTITION BY Col1, Col2, Col3 
                            ORDER by Col1, Col2, Col3, [Year], [Year+n] ) 
        WHEN 
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3 
                                ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 3 THEN
                Result
        END AS Row3Result

from @Table
)
SELECT Col1
    ,Col2
    ,col3
    ,[year]
    ,[Year+n]
    ,Result
    ,Row1Result     --For debug/display purposes only - remove from final answer
    ,Row2Result     --For debug/display purposes only - remove from final answer
    ,Row3Result     --For debug/display purposes only - remove from final answer
    ,CASE 
        WHEN Row2Result &gt; Row1Result
            THEN 'Up'
        WHEN Row2Result = Row1Result
            THEN 'NoChg'
        ELSE 'Down'
        END + CASE 
        WHEN Row3Result &gt; Row2Result
            THEN 'Up'
        WHEN Row3Result = Row2Result
            THEN 'NoChg'
        ELSE 'Down'
        END AS Classification
FROM _CTE
ORDER BY Col1
    ,Col2
    ,col3 DESC




| Col1 | Col2 | col3 | year | Year+n | Result | Row1Result | Row2Result | Row3Result | Classification |
|------|------|------|------|--------|--------|------------|------------|------------|----------------|
| A    | UK   | 1Z   | 2015 | 0      | 10     | 10         | 20         | 30         | UpUp           |
| A    | UK   | 1Z   | 2015 | 1      | 20     | 10         | 20         | 30         | UpUp           |
| A    | UK   | 1Z   | 2015 | 2      | 30     | 10         | 20         | 30         | UpUp           |
| A    | UK   | 1Y   | 2015 | 0      | 40     | 40         | 35         | 40         | DownUp         |
| A    | UK   | 1Y   | 2015 | 1      | 35     | 40         | 35         | 40         | DownUp         |
| A    | UK   | 1Y   | 2015 | 2      | 40     | 40         | 35         | 40         | DownUp         |


After removing the extra 'RowResult' columns that I used to validate my results, I get the results you're looking for.

| Col1 | Col2 | col3 | year | Year+n | Result | Classification |
|------|------|------|------|--------|--------|----------------|
| A    | UK   | 1Z   | 2015 | 0      | 10     | UpUp           |
| A    | UK   | 1Z   | 2015 | 1      | 20     | UpUp           |
| A    | UK   | 1Z   | 2015 | 2      | 30     | UpUp           |
| A    | UK   | 1Y   | 2015 | 0      | 40     | DownUp         |
| A    | UK   | 1Y   | 2015 | 1      | 35     | DownUp         |
| A    | UK   | 1Y   | 2015 | 2      | 40     | DownUp         |