Latest (or Highest-Scoring) Quiz Attempt per Learner

Problem

The quizattempts table contains every attempt a learner has ever made at a quiz — including in-progress (status = 'started'), abandoned, and resumed attempts. For most reporting questions (pass/fail dashboards, score distributions, completion certificates) you really want a single canonical row per learner per quiz.

Quizzes are identified by topicid in this table (each Quiz Page is a topic), so the partition key for "one row per learner per quiz" is (userid, topicid).

Two business definitions are common:

• **Most recent attempt** — reports on the learner's *current* state ("did they pass the last time they tried?").
• **Highest-scoring attempt** — the rule for most graded courses ("best attempt counts").

Solution

-- Most recent finished attempt per learner per quiz
WITH ranked AS (
  SELECT
    quizattempts.*,
    ROW_NUMBER() OVER (
      PARTITION BY userid, topicid
      ORDER BY createdat DESC
    ) AS attempt_rank
  FROM quizattempts
  WHERE status = 'finished'
)
SELECT *
FROM ranked
WHERE attempt_rank = 1;

-- Highest-scoring finished attempt per learner per quiz
-- (ties broken by most recent)
WITH ranked AS (
  SELECT
    quizattempts.*,
    ROW_NUMBER() OVER (
      PARTITION BY userid, topicid
      ORDER BY grade DESC NULLS LAST, createdat DESC
    ) AS score_rank
  FROM quizattempts
  WHERE status = 'finished'
)
SELECT *
FROM ranked
WHERE score_rank = 1;

How it works

• PARTITION BY userid, topicid groups attempts by learner and quiz.
• ORDER BY createdat DESC (or grade DESC, createdat DESC) picks the winner per group.
• status = 'finished' excludes in-progress and abandoned attempts — these have null grades and would skew ranking.

Variations

• **Per course rather than per quiz:** swap topicid for courseid in the partition.
• **Joined to learner detail:** LEFT JOIN users after the CTE on userid.
• **Per attempt response details:** join the resulting id set back to quizattemptsresponses — and apply the historical correct recalculation if needed.