CODEWITHSUNDEEP
mysqlsql
jx12345
jx12345

Reputation: 1670

SQL Query compare data in different rows

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?

Upvotes: 3

Views: 2543

Answers (4)

Abraham
Abraham

Reputation: 548

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

Upvotes: 2

gmm
gmm

Reputation: 478

The following query will give you the number of wins for member_id 1.

select count(*) as number_of_wins
    from member_games as mg1
    where member_id = 1
    and not exists (select 1
        from member_games as mg2
        where mg2.game_id = mg1.game_id
        and mg2.member_id <> mg1.member_id
        and mg2.score >= mg1.score);

The following query will give you the list of members with their respective number of wins.

select m.*,
    (select count(*) from member_games as mg1
        where member_id = m.member_id
        and not exists (select 1
            from member_games as mg2
            where mg2.game_id = mg1.game_id
            and mg2.member_id <> mg1.member_id
            and mg2.score >= mg1.score)
    ) as number_of_wins
    from members as m;

These queries do not consider ties for first place as wins.

I created a MySQL SQL Fiddle demo and a SQL Server SQL Fiddle demo.

Upvotes: 1

Dale M
Dale M

Reputation: 2473

This should do it (SQL Server syntax)

SELECT member_id
      ,COUNT(*) Wins
FROM   (
       SELECT member_id
             ,RANK() OVER (PARTITION BY Game_Id ORDER BY score DESC) Position
       FROM  member_games
       ) Positions
WHERE Position=1
      AND
      member_id=1
GROUP BY member_id

The RANK() allows for the possibility of ties which are possible (if unlikely) in Scrabble. ROW_NUMBER() may offer a small performance enhancement in games where a tie is not possible.

This method can be easily expanded to show all placings as

SELECT member_id
      ,Position
      ,COUNT(*) Games
FROM   (
       SELECT member_id
             ,RANK() OVER (PARTITION BY Game_Id ORDER BY score DESC) Position
       FROM  member_games
       ) Positions
WHERE member_id=1
GROUP BY member_id
        ,Position

See this SQL Fiddle

Upvotes: 1

Orangecrush
Orangecrush

Reputation: 1990

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

Upvotes: 4

Related Questions