MySQL nested query counting

Question

A bit of background info; this is an application that allows users to created challenges and then vote on those challenges (bog standard userX-vs-userY type application).

The end goal here is to get a list of 5 users sorted by the number of challenges they have won, to create a type of leaderboard. A challenge is won by a user if it's status = expired and the user has > 50 votes for that challenge (challenges expire after 100 votes in total).

I'll simplify things a bit here, but essentially there are three tables:

users
- id
- username
- ...
challenges
- id
- issued_to
- issued_by
- status
challenges_votes
- id
- challenge_id
- user_id
- voted_for

So far I have an inner query which looks like:

SELECT `challenges`.`id`
FROM `challenges_votes`
LEFT JOIN `challenges` ON (`challenges`.`id` = `challenges_votes`.`challenge_id`)
WHERE `voted_for` = 1
WHERE `challenges`.`status` = 'expired'
GROUP BY `challenges`.`id`
HAVING COUNT(`challenges_votes`.`id`) > 50

Which in this example would return challenge IDs that have expired and where the user with ID 1 has > 50 votes for.

What I need to do is count the number of rows returned here, apply it to each user from the users table, order this by the number of rows returned and limit it to 5.

To this end I have the following query:

SELECT `users`.`id`, `users`.`username`, COUNT(*) AS challenges_won
FROM (
    SELECT `challenges`.`id`
    FROM `challenges_votes`
    LEFT JOIN `challenges` ON (`challenges`.`id` = `challenges_votes`.`challenge_id`)
    WHERE `voted_for` = 1
    GROUP BY `challenges`.`id`
    HAVING COUNT(`challenges_votes`.`id`) > 0
) AS challenges_won, `users`
GROUP BY `users`.`id`
ORDER BY challenges_won
LIMIT 5

Which is kinda getting there but of course the voted_for user ID here is always 1. Is this even the right way to go about this type of query? Can anyone shed any light on how I should be doing it?

Thanks!

Hi, can you please provide more details about your table structure? I thought the challenges_votes would have the user_id, but I can't find it in your query. — Gerardo Lima
– Gerardo Lima, Commented May 3, 2012 at 16:56
The voted_for column in challenges_votes is the user ID of who the vote is for, whereas user_id is the ID of the user who placed the vote itself (which isn't relevant in this query). — paulbennett
– paulbennett, Commented May 4, 2012 at 7:49

Gerardo Lima · Accepted Answer · 2012-05-04 09:05:48Z

5

I guess the following script will solve your problem:

-- get the number of chalenges won by each user and return top 5
SELECT usr.id, usr.username, COUNT(*) AS challenges_won
FROM users usr
JOIN (
    SELECT vot.challenge_id, vot.voted_for
    FROM challenges_votes vot
    WHERE vot.challenge_id IN (       -- is this check really necessary?
        SELECT cha.id                 -- if any user is voted 51 he wins, so
        FROM challenges cha           -- why wait another 49 votes that won't
        WHERE cha.status = 'expired'  -- change the result?
    )                                 -- 
    GROUP BY vot.challenge_id
    HAVING COUNT(*) > 50
) aux ON (aux.voted_for = usr.id)
GROUP BY usr.id, usr.username
ORDER BY achallenges_won DESC LIMIT 5;

Please allow me to propose a small consideration to the condition to close a challenge: if any user wins after 51 votes, why is it necessary to wait another 49 votes that will not change the result? If this constraint can be dropped, you won't have to check challenges table and this can improve the query performance -- but, it can worsen too, you can only tell after testing with your actual database.

answered May 4, 2012 at 9:05

Gerardo Lima

6,7613 gold badges34 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

paulbennett Over a year ago

Perfect - thanks! We're leaving challenges open until they reach a total of 100 votes so that at some point we can count total votes for a user (where a user can have up to 100 votes per challenge).

Collectives™ on Stack Overflow

MySQL nested query counting

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related