Aggregating row number SQL queries

Question

I have this 3 SQL queries: A:

SET @row_number = 0; 
SELECT 
    (@row_number:=@row_number + 1) AS visit_ranking, 
    creator_id
FROM
    db.games
ORDER BY db.games.visits  desc

B:

SET @row_number = 0; 
SELECT 
    (@row_number:=@row_number + 1) AS favorite_ranking, 
    creator_id
FROM
    db.games
ORDER BY db.games.favorite_count  desc

C:

SET @row_number = 0; 
SELECT 
    (@row_number:=@row_number + 1) AS upvote_ranking, 
    creator_id
FROM
    db.games
ORDER BY db.games.up_votes  desc

all of them works fine separately, but I want to create a single query out of them that will select creator_id,visit_ranking,favorite_ranking,upvote_ranking. but I dont how to do it, could you help?

I have tried this join query:

SELECT
    u.creator_id,
    u.upvote_ranking,
    f.favorite_ranking,
    v.visit_ranking
FROM (
    SELECT
        (@row_number_upvotes := @row_number_upvotes + 1) AS upvote_ranking,
        creator_id,
        up_votes
    FROM
        db.games
    ORDER BY
        db.games.up_votes DESC
) AS u
JOIN (
    SELECT
        (@row_number_favorites := @row_number_favorites + 1) AS favorite_ranking,
        creator_id,
        favorite_count
    FROM
        db.games
    ORDER BY
        db.games.favorite_count DESC
) AS f ON u.creator_id = f.creator_id
JOIN (
    SELECT
        (@row_number_visits := @row_number_visits + 1) AS visit_ranking,
        creator_id,
        visits
    FROM
        db.games
    ORDER BY
        db.games.visits DESC
) AS v ON u.creator_id = v.creator_id;

but it failed miserably

Answer 1

You are using an outdated syntax. Working with count variables in SQL was necessary when MySQL did not yet feature window functions, such as RANK, DENSE_RANK and ROW_NUMBER.

Here is a query with the up-to-date syntax. I am using DENSE_RANK here in order to give the same rank on ties.

SELECT 
  creator_id,
  DENSE_RANK() OVER (ORDER BY db.games.visits DESC) AS visit_ranking,
  DENSE_RANK() OVER (ORDER BY db.games.favorite_count DESC) AS favorite_ranking,
  DENSE_RANK() OVER (ORDER BY db.games.up_votes DESC) AS upvote_ranking
FROM db.games
ORDER BY creator_id;

See https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_rank