CODEWITHSUNDEEP
mysql
Slawek
Slawek

Reputation: 23

MySQL find user rank for each category

Let's say I have the following table:

 user_id | category_id | points
 -------------------------------
       1 |           1 | 4
       2 |           1 | 2
       2 |           1 | 5
       1 |           2 | 3
       2 |           2 | 2
       1 |           3 | 1
       2 |           3 | 4
       1 |           3 | 8

Could someone please help me to construct a query to return user's rank per category - something like this:

user_id | category_id | total_points | rank
-------------------------------------------
      1 |           1 |            4 |    2
      1 |           2 |            3 |    1
      1 |           3 |            9 |    1
      2 |           1 |            7 |    1
      2 |           2 |            2 |    2
      2 |           3 |            4 |    2

Upvotes: 1

Views: 507

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

First, you need to get the total points per category. Then you need to enumerate them. In MySQL this is most easily done with variables:

SELECT user_id, category_id, points,
       (@rn := if(@cat = category_id, @rn + 1,
                  if(@cat := category_id, 1, 1)
                 )
       ) as rank
FROM (SELECT u.user_id, u.category_id, SUM(u.points) as points
      FROM users u
      GROUP BY u.user_id, u.category_id
     ) g cross join
     (SELEct @user := -1, @cat := -1, @rn := 0) vars
ORDER BY category_id, points desc;

Upvotes: 3

spencer7593
spencer7593

Reputation: 108400

MySQL doesn't have analytic functions like other databases (Oracle, SQL Server) which would be very convenient for returning a result like this.

The first three columns are straightforward, just GROUP BY user_id, category_id and a SUM(points).

Getting the rank column returned is a bit more of a problem. Aside from doing that on the client, if you need to do that in the SQL statement, you could make use of MySQL user-defined variables.

SELECT @rank := IF(@prev_category = r.category_id, @rank+1, 1) AS rank 
     , @prev_category := r.category_id AS category_id
     , r.user_id
     , r.total_points
  FROM (SELECT @prev_category := NULL, @rank := 1) i 
 CROSS
  JOIN ( SELECT s.category_id, s.user_id, SUM(s.points) AS total_points
           FROM users s
         GROUP BY s.category_id, s.user_id
         ORDER BY s.category_id, total_points DESC
       ) r
ORDER BY r.category_id, r.total_points DESC, r.user_id DESC

The purpose of the inline view aliased as i is to initialize user defined variables. The inline view aliased as r returns the total_points for each (user_id, category_id).

The "trick" is to compare the category_id value of the previous row with the value of the current row; if they match, we increment the rank by 1. If it's a "new" category, we reset the rank to 1. Note this only works if the rows are ordered by category, and then by total_points descending, so we need the ORDER BY clause. Also note that the order of the expressions in the SELECT list is important; we need to do the comparison of the previous value BEFORE it's overwritten with the current value, so the assignment to @prev_category must follow the conditional test.

Also note that if two users have the same total_points in a category, they will get distinct values for rank... the query above doesn't give the same rank for a tie. (The query could be modified to do that as well, but we'd also need to preserve total_points from the previous row, so we can compare to the current row.

Also note that this syntax is specific to MySQL, and that this is behavior is not guaranteed.

If you need the columns in the particular sequence and/or the rows in a particular order (to get the exact resultset specified), we'd need to wrap the query above as an inline view.

SELECT t.user_id
     , t.category_id
     , t.total_points
     , t.rank 
  FROM (
         SELECT @rank := IF(@prev_category = r.category_id, @rank+1, 1) AS rank 
              , @prev_category := r.category_id AS category_id
              , r.user_id
              , r.total_points
           FROM (SELECT @prev_categor := NULL, @rank := 1) i 
          CROSS
           JOIN ( SELECT s.category_id, s.user_id, SUM(s.points) AS total_points
                    FROM users s
                  GROUP BY s.category_id, s.user_id
                  ORDER BY s.category_id, total_points DESC
                ) r
         ORDER BY r.category_id, r.total_points DESC, r.user_id DESC
      ) t
  ORDER BY t.user_id, t.category_id

NOTE: I've not setup a SQL Fiddle demonstration. I've given an example query which has only been desk checked.

Upvotes: 0

Michael Robinson
Michael Robinson

Reputation: 29498

You want to get the SUM of points for each unique category_id:

SELECT u.user_id, u.category_id, SUM(u.points)
FROM users AS u
GROUP BY uc.category_id

Upvotes: 1

Related Questions