How to nullify result in OVER() if the interval isn't "full"

Question

Let's say I have the following dataset and query:

WITH results as (
    SELECT 'DAL' as team, 2010 as season, 6 as wins union
    SELECT 'DET' as team, 2010 as season, 6 as wins union
    SELECT 'DET' as team, 2011 as season, 10 as wins union
    SELECT 'DET' as team, 2012 as season, 4 as wins union
    SELECT 'DET' as team, 2013 as season, 7 as wins union
    SELECT 'DET' as team, 2014 as season, 11 as wins union
    SELECT 'DET' as team, 2015 as season, 7 as wins union
    SELECT 'DET' as team, 2016 as season, 9 as wins union
    SELECT 'DET' as team, 2017 as season, 9 as wins union
    SELECT 'DET' as team, 2018 as season, 6 as wins union
    SELECT 'DET' as team, 2019 as season, 3 as wins
) SELECT team, season, wins,
    AVG(wins) OVER (partition by team order by season rows between 2 preceding and current row) AS avg_wins_last_two_seasons
from results;

This will return the following results:

# team, season, wins, avg_wins_last_two_seasons
DAL, 2010, 6, 6.0000
DET, 2010, 6, 6.0000
DET, 2011, 10, 8.0000

However, if the size of the window isn't 'complete' - i.e., there aren't two preceding rows -- then I would like the results to be NULL instead of averaging over the decreased window size -- for example, for 2010 it calculates the avg as [6]/1 = 6 but I want it to calculate as: [NULL, NULL, 6] / 3 = NULL. How would I do this?

Answer 1

You can use row_number() and a case expression:

select team, season, wins,
    case when row_number() over(partition by team order by season) > 2
        then avg(wins) over (
            partition by team 
            order by season 
            rows between 2 preceding and current row
        ) 
    end as avg_wins_last_two_seasons
from results;

This ensures that there are least two preceding rows before computing thte window average; if there is not, then this returns null instead.