sqlmysqlgreatest-n-per-group

Check that I have the highest score for each user submission


This is a hypothetical scenario where:

  1. We have a list of games
  2. For each game, users can submit their scores
  3. I, as the 'Admin', have to make a submission with a higher score for EACH user
    • It doesn't matter if I have the highest score for that game, I HAVE to create a submission for each user

For example, here is a list of scores for Game 1

id  game_id     user_id     opponent_id     score
1   1           1           2               1003
2   1           2           null            1002
3   1           3           null            1001

Submissions where opponent_id is NULL are the ones that I have to beat - but I don't have to beat the same user twice, as long as I have a submission for EACH user.

So, in the above example, although I have a higher score in general, I only have a submission against opponent_id = 2, so that means I need another one for opponent_id = 3

How do I get a list of all the games where I'm not the best player? That means all games where a user has a submission and I don't have one that is higher than his specifically.

What I need to do is:

  1. Get the highest score for each user where opponent_id = NULL
  2. Check that I have score for each of those users that is higher than their highest score

I can get step 1, but I'm stumped by step 2

The output would be a list of all games where a player has put a score I need to beat.

A chess game analogy would be that each game is a chess game and I can play each game with multiple players. What I want to see is if there's a game where it's (in essence) my turn. Hope this makes sense.

The wording is a bit weird but here is a fiddle that might make it more clear : https://dbfiddle.uk/xUxfj4XQ

Is this even possible with this database structure? Is there something that can be changed to make it easier/more performant?

EDIT: Based on @user1191247 submission, this is what I got

SELECT *
FROM games
WHERE id IN (SELECT game_id
             FROM (SELECT game_id, user_id, MAX(score) AS max_score
                   FROM scores
                   WHERE opponent_id IS NULL
                   GROUP BY game_id, user_id) g
             WHERE NOT EXISTS (SELECT 1
                               FROM scores
                               WHERE game_id = g.game_id
                                 AND opponent_id = g.user_id
                                 AND score > g.max_score)
             )

Solution

  • I think this meets the criteria set out in your two steps, but I am not sure about the apparent contradiction mentioned in the comments above.

    SELECT *
    FROM (
      SELECT game_id, user_id, MAX(score) AS max_score
      FROM scores
      WHERE opponent_id IS NULL
      AND user_id <> 1
      GROUP BY game_id, user_id
    ) g
    WHERE NOT EXISTS (
      SELECT 1 FROM scores
      WHERE game_id = g.game_id
      AND user_id = 1
      AND opponent_id = g.user_id
      AND score > g.max_score
    );
    

    For the data in your fiddle this outputs:

    game_id user_id max_score
    1 3 1001
    3 3 1001
    4 2 1001
    4 3 1002

    Here's an updated db<>fiddle.