DECLARE @DATA TABLE
(
ID NCHAR(1) NOT NULL,
AVARAGE FLOAT NOT NULL
)
INSERT @DATA(ID, AVARAGE)
SELECT 'A', 60.0
UNION ALL
SELECT 'B', 62.0
UNION ALL
SELECT 'C', 63.0
UNION ALL
SELECT 'D', 59.0
UNION ALL
SELECT 'E', 58.0
UNION ALL
SELECT 'F', 60.0
UNION ALL
SELECT 'G', 61.0
;
WITH ORDERED_TABLE AS
(
SELECT ID, AVARAGE, ROW_NUMBER() OVER (ORDER BY ID) AS ROWNUMBER FROM @DATA
)
SELECT
OT_OUT.ID,
OT_OUT.AVARAGE,
CASE WHEN OT_OUT.ROWNUMBER < 3
THEN NULL
ELSE
(
SELECT AVG(AVARAGE)
FROM ORDERED_TABLE AS OT_IN
WHERE OT_IN.ROWNUMBER IN (OT_OUT.ROWNUMBER, OT_OUT.ROWNUMBER - 1, OT_OUT.ROWNUMBER - 2)
)
END AS CONSECUTIVE_AVARAGE
FROM ORDERED_TABLE AS OT_OUT
WITH ORDERED_TABLE AS, declares a CTE where you add to every row a row-number so you can say which hare the previous two rows to use with average computation...
The second part is a simple query based on this CTE to do the computation...