Interview Question Bank · GROUP BY two columns · MAX · NULL attempts
2. Best quiz score with retakes
EasyEducationQuestion #218
Concept: GROUP BY two columns · MAX · NULL attemptsshow
When learners can retake a quiz, there's one row per attempt, and the question decides the aggregate: MAX for the best try, AVG for typical performance. Group by both the person and the quiz so each pair collapses to one row. MAX skips NULLs, but COUNT(*) counts every row, so an attempt that was never submitted still adds to the count unless you filter it out.
Example (different data):
SELECT driver_id, track, COUNT(*) AS laps, MIN(lap_seconds) AS best_lap FROM practice_laps WHERE lap_seconds IS NOT NULL GROUP BY driver_id, track ORDER BY driver_id, track;
Your task
Brightpath Academy lets students retake any quiz as often as they like, and certificates use each student's best score. A tutor wants that number for every student and quiz.
For every student and quiz with at least one submitted attempt (score not NULL), return student (name), quiz (quiz title), attempts (submitted attempts only) and best_score (highest score). Sort by student, then quiz.
Starting Postgres in your browser…
Checking your access…