SELECT
  user_id,
  team_id,
  highestScore,
  RANK() OVER (PARTITION BY team_id ORDER BY highestScore DESC) AS teamRank,
  RANK() OVER (ORDER BY highestScore DESC) AS globalRank
FROM (
  SELECT
   user_id,
   team_id,
   MAX(score) AS highestScore
  FROM score
  GROUP BY user_id, team_id
) AS t
ORDER BY team_id