For your first query, why is there no order by statement？
In your second query,
OVER (ORDER BY (DATEADD(year, -2, GETDATE())) ROWS BETWEEN 10 PRECEDING CURRENT ROW)
This sentence cannot get 10 rows of data from 2 years ago.Please refer to this document for more details about over clause.
You need to make sure that you get 10 rows of data from 2 years ago,and then calculate avg.
Maybe you can have a try like this:
;WITH CTE AS ( SELECT TOP (10) [timestamp],[COLUMN_NAME] FROM [DATABASE].[dbo].[TABLE] WHERE timestamp > DATEADD(YEAR, -2, GETDATE()) ORDER BY [timestamp] )SELECT AVG(COLUMN_NAME)AS COLUMN_NAME_24MO FROM CTE
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.