MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE score (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(50),
    world_num INT,
    level_num INT,
    user_rank  INT
);
INSERT INTO score (user_id, world_num, level_num, user_rank ) VALUES
('userId1', 1, 1, 3),
('userId1', 1, 2, 1),
('userId1', 5, 15, 12),
('userId1', 2, 5, 2),
('userId2', 1, 1, 1),
('userId2', 1, 2, 2),
('userId2', 5, 15, 4),
('userId2', 2, 5, 1),
-- Add more sample data as needed
('userId3', 1, 1, 1),
('userId3', 1, 2, 1),
('userId3', 1, 3, 2),
('userId3', 2, 5, 1),
('userId4', 1, 1, 1),
('userId4', 1, 2, 1),
('userId4', 1, 3, 2),
('userId4', 2, 5, 2),
('userId4', 3, 6, 3);

Records: 17  Duplicates: 0  Warnings: 0


SELECT user_id,
       SUM(user_rank  = 1) AS rank1Count,
       SUM(user_rank  = 2) AS rank2Count,
       SUM(user_rank  = 3) AS rank3Count,
       SUM(user_rank  = 4) AS rank4Count,
       SUM(user_rank  = 5) AS rank5Count
FROM score
GROUP BY user_id
ORDER BY rank1Count DESC, rank2Count DESC, rank3Count DESC, rank4Count DESC, rank5Count DESC;


user_id
rank1Count
rank2Count
rank3Count
rank4Count
rank5Count


userId3
3
1
0
0
0

userId4
2
2
1
0
0

userId2
2
1
0
1
0

userId1
1
1
1
0
0


-- Dynamic SQL for Overall Ranking list with dynamic rank counts
SET @sql = CONCAT('
    SELECT user_id, ', (
        SELECT GROUP_CONCAT(
            CONCAT(
                'MAX(CASE WHEN user_rank = ', user_rank,
                ' THEN rankCount ELSE 0 END) AS rank', user_rank, 'Count'
            )
            ORDER BY user_rank
        )
        FROM (
            SELECT DISTINCT user_rank FROM score
        ) AS subquery
    ), '
    FROM (
        SELECT user_id, user_rank, COUNT(*) AS rankCount
        FROM score
        GROUP BY user_id, user_rank
    ) AS counts
    GROUP BY user_id
    ORDER BY ', (
        SELECT GROUP_CONCAT(CONCAT('rank', user_rank, 'Count DESC') ORDER BY user_rank)
        FROM (
            SELECT DISTINCT user_rank FROM score
        ) AS subquery
    )
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Statement prepared




user_id
rank1Count
rank2Count
rank3Count
rank4Count
rank12Count


userId3
3
1
0
0
0

userId4
2
2
1
0
0

userId2
2
1
0
1
0

userId1
1
1
1
0
1