MySQL for counting how many top 10 scores a user has

Question

I have a MySQL table with quizzes, and a table that stores results for those quizzes. A result row has a user ID, a quiz ID, (edit: timestamps,) and a numeric score. Users can take the quiz more than once, but we only count the first time they take each quiz.

I'm trying to make a query that will tell me how many times each user has appeared in the top 10 of a quiz (assuming the quiz is closed), and rank those users by how many times they've been in that range. This would allow me to display an all-time ranking based on how they finish.

Any ideas on how I can do this?

Here are relevant parts of the schema.

Quiz:

1. ID
2. Open (timestamp)
3. Close (timestamp)

User:

1. ID
2. Email, name, etc. as profile info

Quiz Result:

1. ID
2. Quiz ID
3. User ID
4. Time Start (timestamp)
5. Time End (timestamp)
6. Score (integer)

My first attempt was like this:

SELECT
distinct least(user_id, time_end) as user_score, result_id, quiz_id, user_id, score, time_end - time_start as duration
FROM results
WHERE time_end != 0 AND user_id != 1 AND user_id != 0
GROUP BY quiz_id, user_score
ORDER BY user_id, time_end ASC;

This is somewhat helpful, but I realized I was going in entirely the wrong direction when I realized I needed a count of times that the user appeared in the top 10. I'm a bit out of my league in moving beyond that, at this point (designer, here).

Answer 1

1. First, you need to establish what scores each user obtained on their first attempt. This is a problem known as the groupwise minimum, which can only be resolved by finding the (quiz, user, time) of every first attempt and joining that back to the results table; this resultset can then be sorted (by quiz and score) to give a ranking of first attempts for each quiz:

   SELECT   quiz_id, user_id, score
   FROM     results NATURAL JOIN (
              SELECT   quiz_id, user_id, MIN(time_end) time_end
              FROM     results
              WHERE    time_end > 0
              GROUP BY quiz_id, user_id
            ) firstAttempts
   ORDER BY quiz_id, score DESC
   

2. It's not enough to take the "top 10" from each of those groups, as multiple users could share the 10th highest score—and it would be arbitrarily unfair to select only some of them. We must therefore find that 10th highest score, then find every user who scored at least that much on their first attempt.

   Unfortunately, MySQL does not support analytic functions—and therefore does not have a neat way to find the "10th record" from each group. Instead, we can use its user-defined variables to count the rank as the above resultset is produced and then filter for the desired rank in an outer query:

   SELECT quiz_id, score
   FROM   (
            SELECT   quiz_id, score,
                     @rank    := IF(quiz_id = @quiz_id, @rank + 1, 1) AS rank,
                     @quiz_id := quiz_id
            FROM     (SELECT @rank := NULL, @quiz_id := NULL) init,
                     results NATURAL JOIN (
                       SELECT   quiz_id, user_id, MIN(time_end) time_end
                       FROM     results
                       WHERE    time_end > 0
                       GROUP BY quiz_id, user_id
                     ) firstAttempts
            ORDER BY quiz_id, score DESC
          ) rankedFirstAttempts
   WHERE  rank = 10
   

3. We can now join this set of 10th highest scores to the first attempts (which we can generate again, as under #1 above), then group by user and sort by the number of times they appear:

   SELECT   user_id, COUNT(*) numberOfTimesInTop10
   FROM     results NATURAL JOIN (
              SELECT   quiz_id, user_id, MIN(time_end) time_end
              FROM     results
              WHERE    time_end > 0
              GROUP BY quiz_id, user_id
            ) firstAttempts JOIN (
              SELECT   quiz_id, score,
                       @rank    := IF(quiz_id = @quiz_id, @rank + 1, 1) AS rank,
                       @quiz_id := quiz_id
              FROM     (SELECT @rank := NULL, @quiz_id := NULL) init,
                       results NATURAL JOIN (
                         SELECT   quiz_id, user_id, MIN(time_end) time_end
                         FROM     results
                         WHERE    time_end > 0
                         GROUP BY quiz_id, user_id
                       ) firstAttempts
              ORDER BY quiz_id, score DESC
            ) rankedFirstAttempts
           ON rankedFirstAttempts.rank     = 10
          AND rankedFirstAttempts.quiz_id  = results.quiz_id
          AND rankedFirstAttempts.score   <= results.score
   GROUP BY user_id
   ORDER BY numberOfTimesInTop10 DESC
   

Pretty horrific, huh?