Top 10 collection completion - a monster in-query formula in MySQL?
- by Andrew Heath
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:
grab user's total owned scenarios from User Collection joined with Game Basic Data and COUNT(gbd.total_scenarios)
grab all recorded plays by COUNT(DISTINCT scenario) for that user
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?
