Top 10 collection completion - a monster in-query formula in MySQL?

Question

I've got the following tables:

User Basic Data (unique)
[userid] [name] [etc]

User Collection (one to one)
[userid] [game]

User Recorded Plays (many to many)
[userid] [game] [scenario] [etc]

Game Basic Data (unique)
[game] [total_scenarios]

I would like to output a table that shows the collection play completion percentage for the Top 10 users in descending order of %:

Output Table

[userid]  [collection_completion]
   3               95%
   1               81%
  24               68%
  etc              etc

In my mind, the calculation sequence for ONE USER is:

1. grab user's total owned scenarios from User Collection joined with Game Basic Data and COUNT(gbd.total_scenarios)
2. grab all recorded plays by COUNT(DISTINCT scenario) for that user
3. Divide all recorded plays by total owned scenarios

So that's 2 queries and a little PHP massage at the end. For a list of users sorted by completion percentage things get a little more complicated.

I figure I could grab all users' collection totals in one query, and all users recorded plays in another, and then do the calcs and sort the final array in PHP, but it seems like overkill to potentially be doing all that for 1000+ users when I only ever want the Top 10.

Is there a wicked monster query in MySQL that could do all that and LIMIT 10? Or is sticking with PHP handling the bulk of the work the way to go in this case?

Answer 1

If I understand what you require correctly, you can try something like

SELECT  userid,
        us.TotalUserscenario / gbd.total_scenarios collection_completion
FROM    (
            SELECT  userid,
                    game,
                    COUNT(scenario) TotalUserscenario
            FROM    UserRecordedPlays urp
            GROUP BY    userid,
                        game
        ) UserScenarios us INNER JOIN
        GameBasicData gbd ON us.game = gbd.game
ORDER BY collection_completion DESC
LIMIT 10