CODEWITHSUNDEEP
mysqlsql
kleopi
kleopi

Reputation: 460

SQL Query to generate a leaderboard and selecting one players information

Assume we have the following MYSQL-Query to generate a leaderboard:

SELECT x.player_id, x.position,x.leaderboard_value

FROM (SELECT player_id,@rownum := @rownum + 1 AS position,leaderboard_value
      FROM table1
      JOIN (SELECT @rownum := 0) r
      WHERE restrictive_value<200
      ORDER BY leaderboard_value DESC) x

which produces a leaderboard like this:

leaderboard

Textfile as CSV: https://www.dropbox.com/s/70xw3ocbonqs98s/sql.csv?dl=0

How do I change or extend the query to deliver a specific players position, and the total amount of positions (highest position)

for above table and player_id=10649 I'd expect a result-table with 1 row containing these fields:

position: 6, totalpositions: 20, percentage= 0.3 (which is 6/20)

Backstory of this is to join this percentage with a different table rank_map(rank_id,minvalue,maxvalue) defining ranks (rank is "9" when this value is between 0.2 and 0.4 for example)

In the end this query should simply return rank: 9 as answer

Thank you very much.

UPDATE:

with Gordon Linoffs answer, using this query: 

SELECT MAX(CASE WHEN player_id = 10649 THEN position END) as play_position,
   COUNT(*) as total_position,
   MAX(CASE WHEN player_id = 10649 THEN position END) / COUNT(*) as ratio
FROM (SELECT t1.*, @rownum := @rownum + 1 AS position
  FROM table1 t1 CROSS JOIN
       (SELECT @rownum := 0) r
  WHERE restrictive_value < 200
  ORDER BY leaderboard_value DESC
 ) x;

I can get this table: second step

A final step is left, which is, how to join it with the rank(rank_id, minvalue,maxvalue) table to only get the rank_id row where ratio is between minvalue and maxvalue?

Upvotes: 3

Views: 1529

Answers (2)

Nick
Nick

Reputation: 147256

You can achieve this by adding COUNT(*) to your subquery JOIN to get the total number of rows, then display the percentage as position/total:

SELECT x.player_id, x.position,x.leaderboard_value, (x.position/x.total) AS percentage
FROM (SELECT total, player_id,@rownum := @rownum + 1 AS position,leaderboard_value
      FROM table1
      JOIN (SELECT @rownum := 0) r
      JOIN (SELECT COUNT(*) AS total FROM table1) c
      WHERE restrictive_value<200
      ORDER BY leaderboard_value DESC) x

Some of the output from your sample data:

player_id   position    leaderboard_value   percentage
2730        1           1090                0.01
1369848     2           1017                0.02
1665922     3           960                 0.03
1607632     4           910                 0.04
1853500     5           909                 0.05
10649       6           883                 0.06
1538490     7           877                 0.07
1898051     8           866                 0.08
1510162     9           828                 0.09
1898129     10          825                 0.1
1863538     11          821                 0.11
1522562     12          806                 0.12
1380267     13          805                 0.13
1404318     14          797                 0.14
8793        15          769                 0.15
21793       16          767                 0.16
14658       17          756                 0.17
1690659     18          729                 0.18
1429094     19          723                 0.19
1727977     20          719                 0.2

SQLFiddle Demo

To get the data for only a specific player, just add a WHERE x.player_id=nnnn clause e.g.

SELECT x.player_id, x.position,x.leaderboard_value, (x.position/x.total) AS percentage
FROM (SELECT total, player_id,@rownum := @rownum + 1 AS position,leaderboard_value
      FROM table1
      JOIN (SELECT @rownum := 0) r
      JOIN (SELECT COUNT(*) AS total FROM table1) c
      WHERE restrictive_value<200
      ORDER BY leaderboard_value DESC) x
WHERE x.player_id = 10649

Output:

player_id   position    leaderboard_value   percentage
10649       6           883                 0.06

To then get their ranking from the rank table, you just need to JOIN it based on percentage (note you have to use the formula as you can't use an alias in a JOIN):

SELECT x.player_id, x.position,x.leaderboard_value, (x.position/x.total) AS percentage, m.rank_id
FROM (SELECT total, player_id,@rownum := @rownum + 1 AS position,leaderboard_value
      FROM table1
      JOIN (SELECT @rownum := 0) r
      JOIN (SELECT COUNT(*) AS total FROM table1) c
      WHERE restrictive_value<200
      ORDER BY leaderboard_value DESC) x
JOIN rank_map m ON m.minvalue <= (x.position/x.total) AND m.maxvalue > (x.position/x.total)
WHERE x.player_id = 10649

Output:

player_id   position    leaderboard_value   percentage  rank_id
10649       6           883                 0.06        3

Updated Demo

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1271061

For this purpose, you can use conditional aggregation:

SELECT MAX(CASE WHEN player_id = 10649 THEN position END) as play_position,
       COUNT(*) as total_position,
       MAX(CASE WHEN player_id = 10649 THEN position END) / COUNT(*) as ratio
FROM (SELECT t1.*, @rownum := @rownum + 1 AS position
      FROM table1 t1 CROSS JOIN
           (SELECT @rownum := 0) r
      WHERE restrictive_value < 200
      ORDER BY leaderboard_value DESC
     ) x;

You can use either COUNT(*) or MAX(position) for the highest position.

Upvotes: 2

Related Questions