Quiz activity and question types in each course

Quiz activity and question types in each course

by Alan Arnold -
Number of replies: 4
At the University of Canberra, our teaching staff use quizzes for formative and summative purposes quite a lot, and their use is growing.

Graph showing the growth in the number of Quiz attempts at the University of Canberra from 2011 to 2014

At the moment, we regularly run the following query (from http://moodle.org/mod/forum/discuss.php?d=205564) which tells us which courses have quizzes and how many submissions there have been in each, but we'd like a better picture of the types of questions that are being used in each quiz.

  

SELECT prefix_course.id id, prefix_course.shortname shortname, prefix_course.fullname fullname, prefix_quiz.name quizname, Count(prefix_quiz_attempts.attempt) attempts

FROM prefix_course prefix_course, prefix_quiz prefix_quiz, prefix_quiz_attempts prefix_quiz_attempts

WHERE prefix_course.id = prefix_quiz.course AND prefix_quiz.id = prefix_quiz_attempts.quiz

GROUP BY prefix_course.id, prefix_quiz.name, prefix_course.shortname, prefix_course.fullname ORDER BY id DESC
It would be great if an SQL guru could extend this query to provide the extra information about question types. Hopefully, it will be useful to someone else too.
Average of ratings: -
In reply to Alan Arnold

Re: Quiz activity and question types in each course

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Just let me rewrite your query into my preferred coding style so I can read it more easily:

SELECT c.id, c.shortname, c.fullname, quiz.name AS quizname, Count(quiza.attempt) attempts

FROM prefix_course        c
JOIN prefix_quiz          quiz  ON c.id    = quiz.course 
JOIN prefix_quiz_attempts quiza ON quiz.id = prefix_quiz_attempts.quiz

GROUP BY c.id, quiz.name, c.shortname, c.fullname
ORDER BY id DESC

Atually, what you ask is not necessarily well defined. Do your quizzes use 'random' questions, and if so, how does that affect the counts you want?

Also, can I assume you want to count number of questions of each type used in each quiz, separately from the number of attempts at each quiz?

Average of ratings: Useful (1)
In reply to Tim Hunt

Re: Quiz activity and question types in each course

by Alan Arnold -

We're interested in using this query to help paint a better picture of assessment practice in the university, rather than delve into the minutiae of each course. In and of itself it won't provide the complete picture but we were hoping that it would give us a better idea of the spread of question types being used. We'd like to be able to identify courses which make heavy use of particular question types so we can use those teachers as mentors for others, say, or proactively identify quizzes that might cause performance concerns if delivered summatively as an online 'exam', say.  So, a little uncertainty in the actual numbers is OK.  

Good point about random questions though.  I have no doubt that some of our quizzes use them, resulting in different attempts in those quizzes having differing numbers of particular question types, so, for each quiz in each course, rather than counting just the number of questions of each type in the quiz, if it's possible to count the number of attempts of each question type, as well as the number of quiz attempts per se (because there will be some questions that won't be attempted), that would give us a more detailed picture.

Any advice you, and others, could give about constructing these queries to help us paint a better 'big' picture would be gratefully received!

Average of ratings: -
In reply to Alan Arnold

Re: Quiz activity and question types in each course

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I can't help thinking that it would be worth your (or one of your colleague's) while to learn some basic SQL, so you can explore the data yourself. It's not that hard if you have that sort of mind. There are various SQL tutorials online, but I don't know which one I would recommend.

Anyway, here are some queries you can try. Note that they are all untested. However they are right in spirit, so should only require minor changes to fix if they fail.

Number of questions of each type in each quiz (if you are using Moodle <= 2.6)

SELECT c.id, c.shortname, c.fullname, quiz.name AS quizname, q.qtype AS question_type, Count(1) AS num_questions

FROM prefix_course                  c
JOIN prefix_quiz                    quiz ON quiz.course = c.id
JOIN prefix_quiz_question_instances qqi  ON qqi.quiz    = quiz.id
JOIN prefix_question                q    ON q.id        = qqi.question

GROUP BY c.id, quiz.name, c.shortname, c.fullname, q.qtype
ORDER BY c.id, quiz.name, Count(1) DESC

The same, if you are using Moodle 2.7+

SELECT c.id, c.shortname, c.fullname, quiz.name AS quizname, q.qtype AS question_type, Count(1) AS num_questions

FROM prefix_course     c
JOIN prefix_quiz       quiz ON quiz.course = c.id
JOIN prefix_quiz_slots qs   ON qs.quizid   = quiz.id
JOIN prefix_question   q    ON q.id        = qs.questionid

GROUP BY c.id, quiz.name, c.shortname, c.fullname, q.qtype
ORDER BY c.id, quiz.name, Count(1) DESC

This is the questions used in the quiz definition, so you will see 'random' in the results.

If you want totals for the whole course (not split into quizzes) it would look like:

SELECT c.id, c.shortname, c.fullname, q.qtype AS question_type, Count(1) AS num_questions

FROM prefix_course     c
JOIN prefix_quiz       quiz ON quiz.course = c.id
JOIN prefix_quiz_slots qs   ON qs.quizid   = quiz.id
JOIN prefix_question   q    ON q.id        = qs.questionid

GROUP BY c.id, c.shortname, c.fullname, q.qtype
ORDER BY c.id, Count(1) DESC

(I have only given the 2.7+ version of the query. If you need a 2.6 version, you will have to work it out yourself.)

If you want to do it by actual questions attempted instead (so random does onto show up) then it would look like:

SELECT c.id, c.shortname, c.fullname, quiz.name AS quizname, q.qtype AS question_type, Count(1) AS num_attempts_at_question_of_this_type

FROM prefix_course            c
JOIN prefix_quiz              quiz  ON quiz.course        = c.id
JOIN prefix_quiz_attempts     quiza ON qs.quizid          = quiz.id
JOIN prefix_question_attempts qa    ON qa.questionusageid = quiza.uniqueid
JOIN prefix_question          q     ON q.id               = qa.questionid

GROUP BY c.id, quiz.name, c.shortname, c.fullname, q.qtype
ORDER BY c.id, quiz.name, Count(1) DESC

There is a third possibility, which is to count the questions in the question bank, but this is probably lease useful (since people may have imported banks of questions, and then only used a few). Also, it is slightly tricky, so I won't try to give it now.

Average of ratings: Useful (1)
In reply to Tim Hunt

Re: Quiz activity and question types in each course

by Alan Arnold -

Many thanks for taking the time Tim.  We're still on 2.5 so the first query worked as is and will tide us over until my colleagues get a chance to upgrade over our summer break.

Average of ratings: -