Average Quiz Score for Quizzes With Three or More Attempts

Easy
126.

Average Quiz Score for Quizzes With Three or More Attempts

EasyAggregation

MySQL

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

ColumnType
attempt_idint
learner_idint
quiz_idint
scoreint

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.

Example

The rows must come back in the order shown.

QuizAttempt

attempt_idlearner_idquiz_idscore
111762
212748
313771
411990
512985
611455
714460
811440
915452

Output

quiz_idattemptsavg_score
4451.75
7360.33
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
attempt_idint
learner_idint
quiz_idint
scoreint

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_idlearner_idquiz_idscore
111762
212748
313771
411990
512985
611455
714460
811440
915452

Output

quiz_idattemptsavg_score
4451.75
7360.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_id

Another 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 →