Selecting a record because its date field is X days after another records date field SQL

Question

I have a table, GameData, with columns; GameID, Date, StartTime, Duration, AteamId, HteamID, Ascore, Hscore, Attendance in my nba db. It basically holds data on NBA games. I want to select all the records for a specific team that are played after X days of rest.

I am having trouble selecting the records so far I have select * from GameData where Date+2 in (Select Date from GameData) and ATeamID = 1030; But this just gets all the records with Date values that are two days apart for a specific team, but it doesn't check if there a records in between. It should only be selecting games that are 2 days after the last played, not games that are simply 2 days after a game regardless if there are games played in between.

Answer 1

You can unpivot the table with union all to generate one row per game and team, then use lag() to get the date of the "previous" game, and finally use that information to filter:

select gd.*
from (
    select gd.*, lag(date) over(partition by team order by date) lag_date 
    from (
        select gd.*, ateam team from gamedata gd
        union all
        select gd.*, hteam team from gamedata gd
    ) gd
) gd
where date > lag_date + interval 2 day