SQL Query compare data in different rows

Question

I had this question at a job interview yesterday and although it seemed pretty straight fwd I couldn't figure it out and it's kept me awake all night thinking about it.

The system records data about a scrabble league there is a members table, a games table and a joining member_games table.

members: member_id, name : pk(member_id)
member_games: game_id, member_id, score : pk(game_id, member_id)
games: game_id, location, date : pk(game_id)

members
1, mick
2, keith
3, charlie

member_games
1, 1, 50
1, 2, 60
2, 1, 45
2, 3, 105
3, 1, 30
3, 3, 120

game
1, london, 2012-12-01
2, richmond, 2012-12-02
3, leeds, 2012-12-03

How do you formulate an SQL query to find out the number of wins for the member_id = 1?

Answer 1

Query to find the number of wins for member_id = 1,

SELECT COUNT(1) "No. of Wins"
  FROM (SELECT game_id, member_id, score
          FROM member_games b
         WHERE score =
               (SELECT max(score) from member_games WHERE game_id = b.game_id)) A
 WHERE member_id = 1;

See this SQLFiddle

Answer 2

The key is to group the member_games first and get the highest score and then join that back to the member_games table to get the member_id.

The fact is that you need a left join to see that Member_id won 0 games.

SELECT
    member_games.member_id
    ,COUNT(BestScore.game_id)
FROM member_games
LEFT JOIN
    (
    SELECT game_id, MAX(score) AS HighestScore FROM member_games GROUP BY Game_ID
    ) BestScore ON member_games.Score = BestScore.HighestScore
AND member_games.game_id = BestScore.game_id
WHERE member_games.member_id = 1
GROUP BY member_games.member_id;

Here it is on SQL Fiddle as MySQL

This solution counts ties as wins, but it should work on any SQL server. The Rank function is available in Microsoft SQL Server 2005 and higher.

For completeness, Here's a more complex query that doesn't count ties as wins:

SELECT
    member_games.member_id
    ,COUNT(BestScore.game_id)
FROM member_games
LEFT JOIN
    (
    SELECT member_games.game_id, HighestScore
    FROM member_games
    LEFT JOIN
        (
          SELECT game_id, MAX(score) AS HighestScore FROM member_games GROUP BY Game_ID
        ) BestScore ON member_games.Score = BestScore.HighestScore
        AND member_games.game_id = BestScore.game_id
        GROUP BY game_id, HighestScore
        HAVING count(1) = 1
    ) BestScore ON member_games.Score = BestScore.HighestScore
WHERE member_games.member_id = 1
GROUP BY member_games.member_id;

Ties as losses on SQL Fiddle as MySQL