Recalculation of quizattemptresponses Correct Column

Problem

Prior to **2025-07-04**, the correct column in the quizattemptresponses table defaulted empty responses to TRUE. For example, if a quiz had 5 questions and a learner answered only 2 before exiting, the remaining 3 unanswered questions were stored as null in the production database — but when the data was processed into the BI warehouse, those empty/null responses were marked as correct = TRUE.

This logic was updated on **2025-07-04**, and for any quiz attempts completed **after that date** empty responses now default to FALSE. However, this fix did **not** retroactively apply to historical records.

Solution

SELECT
  quizattemptresponses.*,
  CASE
    WHEN quizattemptresponses.answer IS NULL
      AND quizattemptresponses.correct = TRUE
      AND quizattemptresponses.createdat < '2025-07-04'
      THEN FALSE
    ELSE quizattemptresponses.correct
  END AS recalculated_correct
FROM quizattemptresponses;

How it works

The CASE flips correct to FALSE **only** when all three conditions hold:

• answer IS NULL — the response was empty.
• correct = TRUE — the row would otherwise be counted as correct.
• createdat < '2025-07-04' — the row was written under the old logic.

All other rows pass through unchanged.

Variations

• **Restrict to a question type:** add AND quizquestiontype = 'multiple_choice' (or your filter of choice) to the CASE if you only want to recalculate certain question types (e.g., exclude essay or manually graded questions).
• **Per-question pass rates:** wrap this as a CTE and aggregate by quizquestionid with AVG(recalculated_correct::int).
• **Replace the column in place:** if you'd rather not retain the original correct, select only the recalculated column: ... AS correct instead of ... AS recalculated_correct.