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