Best custom SQL query with categories?

Question

I am trying to figure out how to get the best users in each category using the mysql query: The My Users table looks like this:

user_id, category_id ... and some other things

My vote table looks like this (each line is one positive vote): reciever_id ... and some other things

I thought that I need to first determine the unique voices of user_id as follows:

reciever_id, votes
1 2
2 6

Then I could order this by the number of votes ... Then select users.whatever, distinct (category_id) from users (this query) WHERE users_id = that_queries_user.id

In any case, I'm probably clearly confused about how to write this request, and any help would be greatly appreciated.

Answer 1

10 :

SELECT  u.*,
        (
        SELECT  COUNT(*)
        FROM    votes v
        WHERE   v.receiver_id = u.user_id
        ) AS score
FROM    users u
ORDER BY
        score DESC
LIMIT 10

:

SELECT  u.*
FROM    (
        SELECT  DISTINCT category_id
        FROM    users
        ) uo
JOIN    users u
ON      u.user_id = 
        (
        SELECT  user_id
        FROM    users ui
        WHERE   ui.category_id = uo.category_id
        ORDER BY
                (
                SELECT  COUNT(*)
                FROM    votes v
                WHERE   v.receiver_id = ui.user_id
                ) DESC
        LIMIT 1
        )

Answer 2

(, , - ):

  Select User_id, category_id, Count(*) voteCnt
  From Users u Join Votes v 
     On v.receiver_id = u.user_id
  Group By User_id, category_id
  Having Count(*) > [Whayever Threshold you want]
  Order By Count(*) Desc

, "" :

  Select User_id, category_id, Sum(votes) voteSum
  From Users u Join Votes v 
     On v.receiver_id = u.user_id
  Group By User_id, category_id
  Having Sum(votes) > [Whayever Threshold you want]
  Order By Sum(votes) Desc