1

I have a table related to game results with more than 300 000 records, where each row keeps player attack. This is simplified table example. I keep an attack id, date, when attack was done, unique player id, player name (changeable by player), and result (bool value, victory or defeat).
attack_id date player_id player_name result
1 2024-03-09 00:00:00 1 cat 1
2 2024-03-10 00:00:00 1 panda 1
3 2024-03-11 00:00:00 2 dog 0
4 2024-03-12 00:00:00 3 wolf 1

I want to show 10 best attack players and my query looks like this:

SELECT
player_id as id,
player_name as name,
count(attack_id) as attacks, 
sum(result) as victory,
( count(attack_id) - sum(result) ) as defeat,
( sum(result) - ( count(attack_id) - sum(result) ) ) as difference
FROM `my_table_name` 
GROUP BY player_id 
ORDER BY difference DESC
LIMIT 10

Query calculates players' victories, defeats and difference between victories and defeats. The issue here related to player name. Player can change a name after some attacks, but this query returns the first player_name (related to player_id), not the last one (current).

Result of this query (according to my example, instead of cat, should be panda, because it was the last player attack name):

id name attacks victory defeat difference
1 cat 2 2 0 2
3 wolf 1 1 0 1
2 dog 1 0 1 -1

How can I solve this issue?

3 Answers 3

0

In MySQL 8 you could use the window function ROW_NUMBEr to get the last name for a user_id

CREATE TABLE my_table_name (
  `attack_id` INTEGER,
  `date` datetime,
  `player_id` INTEGER,
  `player_name` VARCHAR(5),
  `result` INTEGER
);

INSERT INTO my_table_name
  (`attack_id`, `date`, `player_id`, `player_name`, `result`)
VALUES
  ('1', '2024-03-09 00:00:00', '1', 'cat', '1'),
  ('2', '2024-03-10 00:00:00', '1', 'panda', '1'),
  ('3', '2024-03-11 00:00:00', '2', 'dog', '0'),
  ('4', '2024-03-12 00:00:00', '3', 'wolf', '1');
Records: 4  Duplicates: 0  Warnings: 0
WITH CTE AS (SELECT
    player_id, player_name, ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY `date` DESC) rn
FROM  my_table_name)
SELECT
    m.player_id as id,
    MAX(c.player_name) as name,
    count(attack_id) as attacks, 
    sum(result) as victory,
    ( count(attack_id) - sum(result) ) as defeat,
    ( sum(result) - ( count(attack_id) - sum(result) ) ) as difference
FROM `my_table_name` m JOIN CTE c ON m.player_id = c.player_id AND c.rn = 1
GROUP BY m.player_id 
ORDER BY difference DESC
LIMIT 10

id name attacks victory defeat difference
1 panda 2 2 0 2
3 wolf 1 1 0 1
2 dog 1 0 1 -1

fiddle

2
  • Thanks, this solution works. But I guess there should be c.player_name as name, instead of MAX(m.player_name) as name. Max from m returns name with max amount of characters.
    – anton
    Commented Mar 14 at 18:11
  • You are right i corrected it
    – nbk
    Commented Mar 15 at 10:41
0

Your current design has redundancy, and you should separate the name as follow:

Attack table:

  • attack_id
  • date
  • player_id
  • result

Player Table

  • player_id
  • player_name

This way you have a single point of entry for the player name.

1
  • Although valid, I am not sure if it answers the question. Commented Mar 14 at 20:56
0

Splitting the table like Peyman suggested is a good idea. To add to that, you could add a column to the player table called is_current_name and check against that. You could also add start and end time columns if you needed to refer to what name a player had at a particular time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.