Find the winner of each stage
There are tens of different ways to do this in SQL. Lets start with the simple correlated subquery (mind the fancy name, once you see and write a few of them, they are very easy to understand):

select                                -- show
    g.name, g.stage, g.score          -- all data
from                                  -- from
    game as g                         -- the table
where                                 -- where
    not exists                        -- there isn't
        ( select *                    -- any other 
          from game as g2             -- from the same table
          where g2.stage = g.stage    -- and the same stage
            and g2.score &gt; g.score    -- with bigger score
        ) ;


Another simple way would be to first find the biggest score for each stage using GROUP BY (in a subquery, either a derived table or a CTE) and then JOIN back to the original table:

-- using derived table
select      
    g.name, g.stage, g.score 
from      
    game as g 
  join
    ( select stage, max(score) as score
      from game
      group by stage
    ) as m
  on  m.stage = g.stage
  and m.score = g.score ;

-- using CTE
with stage_max as
    ( select stage, max(score) as score
      from game
      group by stage
    ) 
select      
    g.name, g.stage, g.score 
from      
    game as g 
  join
    stage_max as m
  on  m.stage = g.stage
  and m.score = g.score ;


A more modern way would be to use window functions (available in your SQL Server versions), i.e. the RANK() function, so first you get the "rank" of everyone per stage and then select only the ones with rank=1. This can also be done with either a derived table or a CTE:

-- window functions, using derived table
select      
    w.name, w.stage, w.score 
from      
    ( select name, stage, score,
             rnk = rank() over (partition by stage
                                order by score desc)
      from game
    ) as w
where
    w.rnk = 1 ;

-- window functions, using CTE
with ranking as
    ( select name, stage, score,
             rnk = rank() over (partition by stage
                                order by score desc)
      from game
    ) 
select      
    w.name, w.stage, w.score 
from      
    ranking as w 
where
    w.rnk = 1 ;