How to calculate average of values without including the last value (sql)?

Question

I have a table. I partition it by the id and want to calculate average of the values previous to the current, without including the current value. Here is a sample table:

+----+-------+------------+
| id | Value |    Date    |
+----+-------+------------+
|  1 |    51 | 2020-11-26 |
|  1 |    45 | 2020-11-25 |
|  1 |    47 | 2020-11-24 |
|  2 |    32 | 2020-11-26 |
|  2 |    51 | 2020-11-25 |
|  2 |    45 | 2020-11-24 |
|  3 |    47 | 2020-11-26 |
|  3 |    32 | 2020-11-25 |
|  3 |    35 | 2020-11-24 |
+----+-------+------------+

In this case, it means calculating the average of values for dates BEFORE 2020-11-26. This is the expected result

+----+-------+
| id | Value |
+----+-------+
|  1 |    46 |
|  2 |    48 |
|  3 |  33.5 |
+----+-------+

I have calculated it using ROWS N PRECEDING but it appears that this way I average N preceding + last row, and I want to exclude the last row (which is the most recent date in my case). Here is my query:

SELECT ID, 
  (avg(Value) OVER(
      PARTITION BY ID
      ORDER BY Date
      ROWS 9 PRECEDING )) as avg9
FROM t1

Answer 1

Then define your window in full using both the start and ends with BETWEEN:

SELECT ID,
       (AVG(Value) OVER (PARTITION BY ID ORDER BY Date ROWS BETWEEN 9 PRECEDING AND 1 PRECEDING)) AS avg9
FROM t1;