I'm trying do a simple average of the previous two rows. The complicated piece is if a row contains NULL in the score column, I want to do the average of the last 2 NON NULL rows.

Ordering by not null does not work as it puts the current row with the rest of the non null rows. Similar, creating a separate non null row number created the same problem. I assume I have to resolve through an outer join but just checking if anyone is aware of a simpler adjustment to the below code).

Example Data:

```
DECLARE @tbl TABLE 
             (
                 Team varchar(1),
                 date date, 
                 Score int
             );

    INSERT INTO @tbl (Team, Date, Score)
    VALUES
    ('a', '2020/12/05', null),
    ('a', '2020/12/04', null),
    ('a', '2020/12/03', null),
    ('a', '2020/12/02', null),
    ('a', '2020/11/04', '2'),
    ('a', '2020/10/03', '4'),
    ('a', '2020/08/02', '6'),
    ('a', '2020/06/01', '8');

SELECT
    date, 
    avg(SCORE+0.0) OVER (partition by Team ORDER BY Date ASC ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS Average
FROM
    @tbl
ORDER BY 
    Date DESC

```

Output:

```
    +--------+------------+------------+
    |  Row   | Current    | Required   | 
    +--------+------------+------------+
    | 1      | NULL       |     3      |
    | 2      | NULL       |     3      |
    | 3      | 2          |     3      |
    | 4      | 3          |     3      |
    | 5      | 5          |     5      |
    | 6      | 7          |     7      |
    | 7      | 8          |     8      |
    | 8      | NULL       |     NULL   |
    +--------+------------+------------+

```