Recalculation of quizattemptresponses Correct Column

description

Recalculate the correct column for historical data in the quizattemptresponses table.

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 the user answered only 2 before exiting, the remaining 3 unanswered questions were stored as null in the production database. However, 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.

Note that this only applies to reporting data for individual questions; it is not relevant for assessment-level scoring or performance analysis, which relies on the quizattempts table instead.

If you're doing question-level analysis—such as comparing response patterns for a specific question over time or across user cohorts—you can use this recipe to add a new column, recalculated_correct, that adjusts the completion logic for historical data: it flips correct to FALSE only for rows where the answer is blank or null and the original correct value was TRUE and the quiz attempt was created before the release of the updated logic.

If needed, this query can also be easily modified to exclude certain question types (e.g., essay or manually graded questions) from the recalculation. For example, if you only want to apply the logic to multiple-choice questions, you can add a WHERE clause or extend the CASE condition to check the quizquestiontype column.

sql code

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;