Check how many rows in a grouping have the MAX value SQL

My dataset looks like this:

TEAM_ID  PLAYER_ID  NUM_POINTS
     21    39          20
     21    50          10
     21    67          10   
     22    74           0
     22    73           0

I want to get a subset of the data where each team has a clear "winner", that is, if I group by team ID there is exactly ONE player who has more points than all the other players. If there is a tie between two or more players on the team, I do not want them to be included in the subset. I tried out a query but the number or rows I’m getting in my subset seems way too high so I think I’m making a mistake. Here’s my query:

WITH ADD_MAX_POINTS_VALUES_TO_TEAM AS (

    SELECT 
            T1.TEAM_ID,
            MAX(T1.NUM_POINTS) AS MAX_POINTS_FOR_TEAM, 
    FROM MY_TABLE T1
    GROUP BY T1.TEAM_ID

), GET_SUBSET AS (

    SELECT T1.TEAM_ID
           T1.PLAYER_ID
           T2.MAX_POINTS_FOR_TEAM  
    FROM MY_TABLE T1 INNER JOIN ADD_MAX_POINTS_VALUES_TO_TEAM T2
    ON T1.B1_BUS_PRTNR_NBR = T2.B1_BUS_PRTNR_NBR 
    WHERE T1.NUM_POINTS = T2.MAX_POINTS_FOR_TEAM
    GROUP BY 1, 2, 3
    HAVING COUNT(*) = 1 -- > HERE I AM TRYING TO SAY THERE IS ONE UNIQUE PLAYER ON THE TEAM WITH THE MAX SCORE
), 
   SELECT COUNT(*) FROM GET_SUBSET

Any help is appreciated, let me know if I need to provide more info.

Thanks!!

You need to sign in to view this answers