Drill 2 of 12 in Education questions
0

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…