Moodle SQL: All attempt steps in a quiz

attempt-steps.sql

SELECT 
  qa.*, 
  qas_last.*, 
  qas_last.timecreated AS stepdate,
  u.email, 
  u.firstname, 
  u.lastname 
FROM {course_modules} cm
JOIN {quiz_attempts} quiza ON quiza.quiz = cm.instance
JOIN {question_usages} qu ON qu.id = quiza.uniqueid
JOIN {question_attempts} qa ON qa.questionusageid = qu.id
  LEFT JOIN {question_attempt_steps} qas_last ON qas_last.questionattemptid = qa.id 
  /* attach another copy of qas to those rows with the most recent timecreated, using method from https://stackoverflow.com/a/28090544 */
  LEFT JOIN {question_attempt_steps} qas_prev ON qas_last.questionattemptid = qas_prev.questionattemptid 
  AND (
    qas_last.timecreated < qas_prev.timecreated 
    OR (
      qas_last.timecreated = qas_prev.timecreated 
      AND qas_last.id < qas_prev.id
    )
  ) 
  LEFT JOIN {user} u ON qas_last.userid = u.id 
WHERE 
  cm.id = :quiz_id
  AND qas_prev.timecreated IS NULL 
ORDER BY 
  u.username, 
  qas_last.timecreated