Calculating Wins/Losses for NBA Teams

Question

So I have a reporting table of player stats from each game this past NBA season in Snowflake. I'm trying to determine a team's Win/Loss Record and here are the relevant columns where team represents the team the player is currently on. (This sample data is abbreviated to 5 records for each game)

GAME_ID	PLAYER	TEAM	HOME	HOME_FINAL	AWAY	AWAY_FINAL	GAME_TIME
1	Jalen Brunson	NYK	NYK	112	MIN	106	2024-01-01T15:00:00.000000Z
1	Julius Randle	NYK	NYK	112	MIN	106	2024-01-01T15:00:00.000000Z
1	Josh Hart	NYK	NYK	112	MIN	106	2024-01-01T15:00:00.000000Z
1	Donte DiVincenzo	NYK	NYK	112	MIN	106	2024-01-01T15:00:00.000000Z
1	OG Anunoby	NYK	NYK	112	MIN	106	2024-01-01T15:00:00.000000Z
2	Jalen Brunson	NYK	NYK	116	CHI	100	2024-01-03T20:30:00.000000Z
2	Julius Randle	NYK	NYK	116	CHI	100	2024-01-03T20:30:00.000000Z
2	Josh Hart	NYK	NYK	116	CHI	100	2024-01-03T20:30:00.000000Z
2	Donte DiVincenzo	NYK	NYK	116	CHI	100	2024-01-03T20:30:00.000000Z
2	OG Anunoby	NYK	NYK	116	CHI	100	2024-01-03T20:30:00.000000Z

Given the final scores for the home team, away team; and an identifier for the home and away teams I want to find a team's Win/Loss record by month/season/etc. Seems simple enough, but I think with my current logic it's summing every player record in each game as a Win/Loss (so if the NYK lose and had 12 players play my logic picks this up as 12 losses for one game when I need it to pick that up as one loss for the entire game). I'm also not 100% confident that my case statements are set up correctly. What can I change to get the proper output?

SELECT 
    team,
    AWAY_WINS + HOME_WINS as WINS,
    AWAY_LOSSES + HOME_LOSSES as LOSS
FROM (
SELECT 
    game_id,
    team,
    SUM(CASE WHEN away = team AND away_final > home_final THEN 1 ELSE 0 END) AS AWAY_WINS,
    SUM(CASE WHEN home = team AND away_final < home_final THEN 1 ELSE 0 END) AS HOME_WINS,
    SUM(CASE WHEN away = team AND away_final < home_final THEN 1 ELSE 0 END) AS AWAY_LOSSES,
    SUM(CASE WHEN home = team AND away_final > home_final THEN 1 ELSE 0 END) AS HOME_LOSSES
FROM PLAYER_REPORTING_TABLE t1
WHERE MONTH(TO_DATE(t1.GAME_TIME)) = 1 AND YEAR(TO_DATE(t1.GAME_TIME)) = 2024 AND team = 'NYK' 
GROUP BY team, game_id
)
GROUP BY team, wins, loss;

Answer 1

select team,
    sum(case when pts > opponent_pts then 1 else 0 end) as wins,
    sum(case when pts < opponent_pts then 1 else 0 end) as losses
from (
    select distinct game_id, team,
        case when home = team
            then home_final else away_final end as pts, 
        case when home = team
            then away_final else home_final end as opponent_pts
    from PLAYER_REPORTING_TABLE
    --where ...
) as scores
group by team;

That inner query will give one row per team per game. Even though you don't need it later, game_id in this result is important so you don't eliminate games against the same opponent that ended with the same score. It's easy to summarize after that.

https://dbfiddle.uk/7Zm38k-7