Average Quiz Score for Quizzes With Three or More Attempts
An online learning platform's content team reviews quiz difficulty, but an average over one or two attempts says little.
- Difficulty: Easy
- Topics: Aggregation
- Dialect: MySQL
- Problem: #126
Problem statement
An online learning platform's content team reviews quiz difficulty, but an average over one or two attempts says little. Each row of QuizAttempt is one learner's attempt at one quiz, scored out of 100.
For every quiz with at least 3 attempts, return quiz_id, attempts (the number of attempts) and avg_score (the mean score, rounded to 2 decimal places). Quizzes with fewer attempts are left out. Order the rows by avg_score lowest first, then by quiz_id.
Tables
Table: QuizAttempt
| Column | Type |
|---|---|
| attempt_id | int |
| learner_id | int |
| quiz_id | int |
| score | int |
Primary key: attempt_id.
score is a whole number from 0 to 100; a learner may attempt the same quiz more than once and every attempt counts.
Examples
Example 1
QuizAttempt
| attempt_id | learner_id | quiz_id | score |
|---|---|---|---|
| 1 | 11 | 7 | 62 |
| 2 | 12 | 7 | 48 |
| 3 | 13 | 7 | 71 |
| 4 | 11 | 9 | 90 |
| 5 | 12 | 9 | 85 |
| 6 | 11 | 4 | 55 |
| 7 | 14 | 4 | 60 |
| 8 | 11 | 4 | 40 |
| 9 | 15 | 4 | 52 |
Output
| quiz_id | attempts | avg_score |
|---|---|---|
| 4 | 4 | 51.75 |
| 7 | 3 | 60.33 |
How to solve Average Quiz Score for Quizzes With Three or More Attempts
Group the attempts by quiz_id; each group is one quiz, COUNT(*) is its attempts and AVG(score) its mean score. The statement asks only for quizzes with at least three attempts, a condition on the group rather than on a row, so it belongs in HAVING, which is evaluated after the groups are built. WHERE cannot see COUNT(*) at all.
Round the average to two places for display. Ordering by the rounded average with quiz_id as the tie-breaker makes the order unique, which matters because two quizzes can easily share an average.
An equivalent form computes the counts in a derived table and filters them in the outer WHERE; SUM(score) / COUNT(*) is the same mean as AVG. The cost is one pass over the attempts with a hash or sort on quiz_id.
Reference solution (MySQL)
SELECT quiz_id, COUNT(*) AS attempts, ROUND(AVG(score), 2) AS avg_score
FROM QuizAttempt
GROUP BY quiz_id
HAVING COUNT(*) >= 3
ORDER BY avg_score, quiz_idAnother way
SELECT quiz_id, attempts, avg_score FROM (SELECT quiz_id, COUNT(attempt_id) AS attempts, ROUND(SUM(score) / COUNT(*), 2) AS avg_score FROM QuizAttempt GROUP BY quiz_id) t WHERE attempts >= 3 ORDER BY avg_score ASC, quiz_id ASC← Fee Invoices Paid Late or Still Overdue · Learner Sign-ups by E-mail Domain →