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?

sorry, mysql. Will using max() count draws as wins?

jx12345
– jx12345

2013-02-08 05:15:19 +00:00
Commented Feb 8, 2013 at 5:15 — jx12345
– jx12345, Commented Feb 8, 2013 at 5:15
Yes it will count draws as ties.

ElRojo
– ElRojo

2013-02-08 05:20:43 +00:00
Commented Feb 8, 2013 at 5:20 — ElRojo
– ElRojo, Commented Feb 8, 2013 at 5:20

Himanshu · Accepted Answer · 2013-02-08 05:07:23Z

4

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

edited Feb 8, 2013 at 5:07

Himanshu

32.8k32 gold badges117 silver badges137 bronze badges

answered Feb 8, 2013 at 4:58

Orangecrush

1,9902 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Himanshu Over a year ago

Oh so you answered because it is related to Sports. ;)

Orangecrush Over a year ago

@hims056 lol. That's the reputation I have eh? :P Good to see you here though ':)`

ElRojo · Accepted Answer · 2013-02-08 05:36:04Z

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

edited Feb 8, 2013 at 5:36

answered Feb 8, 2013 at 4:58

ElRojo

5748 silver badges32 bronze badges

2 Comments

jx12345 Over a year ago

thanks, is there any way to take draws into account, i think with this they count as wins

ElRojo Over a year ago

Good call. I just edited it to count ties as losses. GMM beat me to it and with fewer characters.

gmm · Accepted Answer · 2013-02-08 05:27:12Z

1

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.

edited Feb 8, 2013 at 5:27

answered Feb 8, 2013 at 5:04

gmm

4883 silver badges13 bronze badges

2 Comments

jx12345 Over a year ago

cool, the solutions using max() count draws as wins, this solution doesn't

gmm Over a year ago

@jx12345: Actually, my original answer did treat draws (ties) as wins, but now that I know you don't want to treat them as wins, I changed > to >= and now it doesn't treat them as wins.

Dale M · Accepted Answer · 2013-02-08 05:11:31Z

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

Collectives™ on Stack Overflow

SQL Query compare data in different rows

4 Answers 4

See this SQLFiddle

2 Comments

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related