I have a table for user records that hold scores (and need to get only users that are members). I need to get the top 5 scores for each user during this year, average the scores and return the top 10 users.
NOTE: Also the user must have a minimum of 5 entries.
SCORE TABLE:
user_id | score | date_submitted
1 99 2017-11-07 22:00:00
2 55 2017-10-33 11:33:35
1 12 2017-09-33 11:33:35
USER TABLE
id | is_member
1 1
2 1
3 0
Here is what I have so far:
SELECT s.user_id,
(SELECT AVG(s.score) FROM score s2 WHERE s2.user_id = s.user_id ORDER BY score DESC LIMIT 5) gr
FROM score s, users u
WHERE u.id = s.user_id
AND u.is_member = 1
AND YEAR(s.date_submitted) = YEAR(CURDATE())
GROUP BY s.user_id
HAVING COUNT(*) >= 5
ORDER BY gr DESC LIMIT 10
This returns:
1242 - Subquery returns more than 1 row
I understand that its the limit in the subquery, I am trying to figure out how to get the top 5 records for that user.