Local Usage

There are a number of reporting options which are built-in and supported. To use the features on this page requires moodle admin permission.

It is often useful to know numbers about STACK usage on your local server. To do this we need to work in the Moodle database. E.g.

1. How many STACK questions were initialised for students during the last time interval?
2. How many attempts those students made to those question during that time on average?
3. Which courses had most attempts and which courses had the most STACK questions in use?
4. Question bank status by course, how many questions were in use, which questions have not been used for a long time?
5. Quiz/activity usage, i.e. in what context are the questions being used and for example how many questions are present in a quiz by average?
6. Duplicate questions, how many copies of the same questions are present in the question banks?

Do get this kind of data you will need the custom sql report.

The number of questions that students have interacted with

That is the number of questions that students have interacted with.

SELECT COUNT(1)
FROM {question_attempts} qa
JOIN {question} q ON q.id = qa.questionid
JOIN {quiz_attempts} quiza ON quiza.uniqueid = qa.questionusageid
WHERE quiza.preview = 0
AND q.qtype = 'stack'
AND qa.timemodified BETWEEN %%STARTTIME%% AND %%ENDTIME%%
And, here is a query that does number of questions attempted (ever) by course

SELECT c.shortname AS Website, COUNT(1) AS Number_of_STACK_questions_attempted

FROM {question_attempts} qa
JOIN {question} q ON q.id = qa.questionid
JOIN {quiz_attempts} quiza ON quiza.uniqueid = qa.questionusageid
JOIN {quiz} quiz ON quiz.id = quiza.quiz
JOIN {course} c ON c.id = quiz.course

WHERE q.qtype = 'stack'
AND quiza.preview = 0

GROUP BY c.shortname

ORDER BY COUNT(1) DESC, c.shortname

Moodle has a built-in report that will count the number of questions of any type in the question bank (report/questioninstances) but I think this is a less useful statistic.

Closing date of quizzes which use STACK

Another useful report, which we use for planning the time of upgrades, is this one, which lists the close date of quizzes uses STACK (including ones that use STACK questions via Moodle's 'Random question' feature:

SELECT DISTINCT
    c.shortname AS Website,
    quiz.name AS iCMA,
    quiz.timeopen AS Open_date,
    quiz.timeclose AS Close_date

FROM {quiz} quiz
JOIN {course} c ON c.id = quiz.course

WHERE to_timestamp(quiz.timeclose) > 'now'::timestamp - '1 month'::interval
AND EXISTS (
    SELECT 1
    FROM {quiz_slots} slot
    JOIN {question} q ON q.id = slot.questionid
    WHERE slot.quizid = quiz.id AND (
        q.qtype = 'stack' OR q.qtype = 'random' AND EXISTS (
            SELECT 1
            FROM {question_categories} qc
            JOIN {question} rq ON rq.category = qc.id
            WHERE qc.id = q.category AND rq.qtype = 'stack'
        )
    )
)
ORDER BY Close_date, Website, iCMA

List all the people who have created STACK questions in your site

One final query, which lists all the people who have created STACK questions in your site (useful if you need to email them about something:

SELECT u.firstname, u.lastname, u.username, u.email, number_created, number_modified

FROM (
    SELECT COALESCE(createdby, modifiedby) AS userid,
           COALESCE(number_created, 0) AS number_created,
        COALESCE(number_modified, 0) AS number_modified
    FROM (

        SELECT createdby, COUNT(1) AS number_created
        FROM {question} WHERE qtype = 'stack'
        GROUP BY createdby

    ) qscreated
    FULL JOIN (

        SELECT modifiedby, COUNT(1) AS number_modified
        FROM {question} WHERE qtype = 'stack'
        GROUP BY modifiedby

    ) qsmodified ON createdby = modifiedby

) comined_counts

JOIN {user} u ON u.id = userid

ORDER BY number_created DESC, number_modified DESC

Further queries

These queries were last updated in April 2020 (and are not actively maintained as a core part of STACK). Please contribute related queries to the developers.