Skip to content

Instantly share code, notes, and snippets.

@georgekinnear

georgekinnear/moodle-sql.md

Created March 12, 2024 08:40
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save georgekinnear/2f731bd3bca6ce97e6bdc202b1a6c77f to your computer and use it in GitHub Desktop.
Save georgekinnear/2f731bd3bca6ce97e6bdc202b1a6c77f to your computer and use it in GitHub Desktop.
Download ZIP
SQL queries for use with Moodle's ad-hoc database plugin
Raw
moodle-sql.md

Summarising student activity

Number of times each student attempted each quiz question

For each quiz in the course, for every student who attempted it, count how many attempts they made at each question.

SELECT q.name AS quiz, MIN(qa.slot) AS slot, que.name AS question, u.id AS student_id, COALESCE( SUM( (que.qtype = 'stack' AND (LOCATE('[score]', qa.responsesummary) > 0)) OR ( que.qtype <> 'stack' AND LENGTH(qa.responsesummary) > 0 ) ), 0) AS 'num_attempts_by_student'

FROM {quiz_attempts} quiza JOIN {quiz} q ON q.id=quiza.quiz JOIN {question_usages} qu ON qu.id = quiza.uniqueid JOIN {question_attempts} qa ON qa.questionusageid = qu.id JOIN {question} que ON que.id = qa.questionid JOIN {user} u ON u.id = quiza.userid

WHERE q.course = :course_id AND que.qtype <> 'description'

GROUP BY quiz, question, u.id

ORDER BY quiz, slot, student_id

Summary of question attempts across all quizzes in a course

For each question in each quiz, how many students attempted it?

Notes:

  • students may make multiple attempts at a quiz/question so we count unique students rather than attempts.
  • we use question id to distinguish the questions
    • the question_attempts "slot" value can go beyond the number of slots in the quiz - that represents when students have used the "try another question like this" to replace a question with a new one (e.g. if there are 10 items in the quiz, and I ask for another version of Q3, it will use slot 11 for the replacement question). I'm not sure how Moodle keeps track of how the slots map on to the position in the quiz (e.g. to know that slot 11 is actually for Q3).
    • presumably using question id would cause problems if you use the "randomly select one of these questions to fill this slot" feature of the moodle quiz.

num_students shows the total number of students who attempted any question in the quiz (should be the same for all questions in the quiz!) num_attempts the total number of attempts at that question made by all students, including multiple attempts num_students_attempting the number of unique students who made at least one attempt at that question

SELECT quiz, slot, question, COUNT(*) AS num_students, SUM(num_attempts_by_student) AS num_attempts, SUM(num_attempts_by_student > 0) AS num_students_attempting

FROM (SELECT q.name AS quiz, MIN(qa.slot) AS slot, que.name AS question, u.id AS student_id, COALESCE( SUM( (que.qtype = 'stack' AND (LOCATE('[score]', qa.responsesummary) > 0)) OR ( que.qtype <> 'stack' AND LENGTH(qa.responsesummary) > 0 ) ), 0) AS 'num_attempts_by_student'

FROM {quiz_attempts} quiza JOIN {quiz} q ON q.id=quiza.quiz JOIN {question_usages} qu ON qu.id = quiza.uniqueid JOIN {question_attempts} qa ON qa.questionusageid = qu.id JOIN {question} que ON que.id = qa.questionid JOIN {user} u ON u.id = quiza.userid

WHERE q.course = :course_id AND que.qtype <> 'description' AND quiza.timestart < :before_date

GROUP BY quiz, question, u.id

ORDER BY quiz, slot, student_id) AS count_student_attempts

GROUP BY quiz, question ORDER BY quiz, slot

Flagged questions: all by a student

All questions flagged by a given student

Based on SQL from https://moodle.org/mod/forum/discuss.php?d=228860

SELECT qc.name AS question_category, q.name AS question, SUM(qa.flagged) AS number_of_times_flagged, CONCAT('%%WWWROOT%%/question/edit.php', CHAR(63), 'cmid=', cm.id, '&category=', qc.id, '%2C', qc.contextid, '&lastchanged=', q.id) AS edit_url, CONCAT('%%WWWROOT%%/mod/quiz/reviewquestion.php', CHAR(63), 'attempt=', quiza.id, '&slot=', qa.slot) AS review_question FROM {question} q JOIN {question_categories} qc ON qc.id = q.category JOIN {question_attempts} qa ON qa.questionid = q.id JOIN {quiz_attempts} quiza ON quiza.uniqueid = qa.questionusageid JOIN {course_modules} cm ON cm.instance = quiza.quiz JOIN {modules} m ON m.id = cm.module WHERE quiza.preview = 0 AND m.name = 'quiz' AND quiza.userid = :student_userid GROUP BY qc.name, q.name, cm.id, qc.id, qc.contextid, q.id ORDER BY SUM(qa.flagged) DESC, qc.name, q.name

Flagged questions: all in a course

For a given course ID, show how many times each question has been "flagged" by users.

SQL from https://moodle.org/mod/forum/discuss.php?d=228860

SELECT qc.name AS question_category, q.name AS question, SUM(qa.flagged) AS number_of_times_flagged, CONCAT('%%WWWROOT%%/question/edit.php', CHAR(63), 'cmid=', cm.id, '&category=', qc.id, '%2C', qc.contextid, '&lastchanged=', q.id) AS edit_url FROM {question} q JOIN {question_categories} qc ON qc.id = q.category JOIN {question_attempts} qa ON qa.questionid = q.id JOIN {quiz_attempts} quiza ON quiza.uniqueid = qa.questionusageid JOIN {course_modules} cm ON cm.instance = quiza.quiz JOIN {modules} m ON m.id = cm.module WHERE quiza.preview = 0 AND m.name = 'quiz' AND cm.course = :course_id GROUP BY qc.name, q.name, cm.id, qc.id, qc.contextid, q.id ORDER BY SUM(qa.flagged) DESC, qc.name, q.name

Question response data

ALL steps in attempts at a given question (by qid)

Get all steps in student attempts at a given question, specified by its questionid

SELECT qa.questionid, qa.variant, qa.responsesummary, qas., qasd., u.firstname, u.lastname FROM {question_attempts} qa LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id LEFT JOIN {user} u ON qas.userid = u.id WHERE qa.questionid = :question_id

All attempts at a given question

SELECT qa., DATE_FORMAT(FROM_UNIXTIME(qa.timemodified), '%Y-%m-%d %H:%i:%s') AS 'timemodified_formatted', qas_last., u.email, u.firstname, u.lastname FROM {question_attempts} qa 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 qa.questionid = :question_id AND qas_prev.timecreated IS NULL ORDER BY u.username, qas_last.timecreated

Quiz attempt data

SELECT quiza.userid, quiza.quiz, quiza.id AS quizattemptid, quiza.attempt, quiza.sumgrades, FROM_UNIXTIME(quiza.timestart), FROM_UNIXTIME(quiza.timefinish), '', qu.preferredbehaviour, '', qa.slot, qa.behaviour, qa.questionid, qa.variant, qa.maxmark, qa.minfraction, qa.flagged, '', qas.sequencenumber, qas.state, qas.fraction, FROM_UNIXTIME(qas.timecreated) AS timecreated, qas.userid, '', qasd.name, qasd.value, qa.questionsummary, qa.rightanswer, qa.responsesummary

FROM {quiz_attempts} quiza JOIN {question_usages} qu ON qu.id = quiza.uniqueid JOIN {question_attempts} qa ON qa.questionusageid = qu.id JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id

WHERE quiza.quiz = :quiz_id AND (qasd.name = '_seed' OR qasd.name = '-submit' OR qasd.name = '-finish' )

ORDER BY quiza.userid, quiza.attempt, qa.slot, qas.sequencenumber, qasd.name

Detailed data about a particular quiz attempt

SELECT quiza.userid, quiza.quiz, quiza.id AS quizattemptid, quiza.attempt, quiza.sumgrades, qu.preferredbehaviour, qa.slot, qa.behaviour, qa.questionid, qa.variant, qa.maxmark, qa.minfraction, qa.flagged, qas.sequencenumber, qas.state, qas.fraction, FROM_UNIXTIME(qas.timecreated), qas.userid, qasd.name, qasd.value, qa.questionsummary, qa.rightanswer, qa.responsesummary

FROM {quiz_attempts} quiza JOIN {question_usages} qu ON qu.id = quiza.uniqueid JOIN {question_attempts} qa ON qa.questionusageid = qu.id JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id

WHERE quiza.id = :attempt_id

ORDER BY quiza.userid, quiza.attempt, qa.slot, qas.sequencenumber, qasd.name

Student responses (answers) to quiz questions

( SELECT q.name AS quiz, qa.slot AS slot, SUM(COALESCE( (que.qtype = 'stack' AND (LOCATE('[score]', qa.responsesummary) > 0)) OR ( que.qtype <> 'stack' AND LENGTH(qa.responsesummary) > 0 ),0)) AS 'studentsAttempting', COUNT(slot) AS 'countOfSlot'

FROM {quiz_attempts} quiza JOIN {quiz} q ON q.id=quiza.quiz JOIN {question_usages} qu ON qu.id = quiza.uniqueid JOIN {question_attempts} qa ON qa.questionusageid = qu.id JOIN {question} que ON que.id = qa.questionid JOIN {user} u ON u.id = quiza.userid

WHERE q.course = :course_id AND que.qtype <> 'description'

GROUP BY quiz, slot )

Miscellaneous

All courses

SELECT * FROM {course}

List course names and ids

Return an alphabetical list of all courses along with ids and shortnames

select fullname, id, shortname from {course} order by fullname asc

Count of questions in use in quizzes across all courses

SELECT DISTINCT c.id as courseid, c.shortname, c.fullname, q.qtype, COUNT(q.id) AS qcount FROM {quiz} quiz JOIN {course} c ON c.id = quiz.course JOIN {quiz_slots} slot ON slot.quizid = quiz.id JOIN {question} q ON q.id = slot.questionid GROUP BY c.id, q.qtype

Find questions that use simp:false in CASText (questiontext/generalfeedback)

These will need to be fixed in light of maths/moodle-qtype_stack#849

For the REGEXP, note that this query plugin does not allow ? or : characters in the query, so we need to build the following regex pattern:

simp\s?:\s?false

as

CONCAT("simp\s", CHAR(63), CHAR(58), "\s", CHAR(63), "false")

SELECT DISTINCT c.id as courseid, c.shortname, c.fullname, concat('%%WWWROOT%%/question/question.php%%Q%%courseid=',c.id,'&id=',q.id) as edit_link, q.* FROM {quiz} quiz JOIN {course} c ON c.id = quiz.course JOIN {quiz_slots} slot ON slot.quizid = quiz.id JOIN {question} q ON q.id = slot.questionid WHERE ((c.fullname LIKE "%2023%") OR (c.fullname LIKE "%HELM%")) AND (q.generalfeedback REGEXP CONCAT("simp\s", CHAR(63), CHAR(58), "\s", CHAR(63), "false") OR q.questiontext REGEXP CONCAT("simp\s", CHAR(63), CHAR(58), "\s", CHAR(63), "false")
)

Find questions that use simp:false in CASText (prt feedback)

Data to help with maths/moodle-qtype_stack#849

SELECT * FROM {qtype_stack_prt_nodes} WHERE truefeedback REGEXP CONCAT("simp\s", CHAR(63), CHAR(58), "\s", CHAR(63), "false") OR truefeedback REGEXP CONCAT("simp\s", CHAR(63), CHAR(58), "\s", CHAR(63), "false")

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment