Week 6 of my experiment working out to what extent gamifying Moodle courses increase student engagement with the platform. This week’s post is aimed at Moodlers who enjoy the technical side of Moodle. In this post I share some MySQL queries on activity completion that go beyond the activity completion report. You can re-use these queries directly in PHPMyAdmin, command line, or using the excellent configurable reports plugin.
Most queries will need information that is found in Moodle web page addresses (URL, or Unique Resource Locator) so it’s important that you understand what to look for. Each page has a unique identifier (or URL), which can be found by either hovering your mouse over the resource/activity you’re interested in (URL should show up at the bottom of your window), or by simply visiting the page and checking the address in your web browser address bar. We’re particularly interested in the ID numbers.
|Hover your mouse over name||Visit the activity|
Examples of Moodle URL’s
Unix Epoch times
Moodle stores all time related data in the database as Unix Epoch time, or the number of seconds that have elapsed since 1st January 1970 at midnight UTC. For example, if a student submitted an assignment on 28th April 2013 at 6.59 am GMT time, then Moodle would store the time stamp for this event as 1367132340 in the database.
|Human readable date & time||Unix Epoch time|
You can find out how to convert Epoch time into human readable date/time by looking at the ‘Number of hits per student, per day’ query below, or by using this online converter.
Here are some of the queries I have used. I am very much a beginner with SQL so keep in mind I might not have adhered to best practices. In all queries, change ‘prefix_’ with the actual prefix for your Moodle database (more often than not, it will be ‘mdl_’) – leave as is if using the configurable reports plugin. You shouldn’t be able to break anything using this but don’t come and blame me if you do 🙂
Number of hits in a course
This MySQL query adds up how many clicks to activity/resources have been recorded in the logs for a specific course.
SELECT course, COUNT(*) AS hits FROM `prefix_log` WHERE `course` = 1693 AND `userid` != 782 GROUP BY `course` ORDER BY `hits` DESC
Change the course ID to suit your needs. In this example, I have removed clicks performed by myself (userid 782) as to not skew the results. You can find your own user ID by visiting your profile and checking the URL.
Number of hits per student
This query calculates the number of hits each student has performed in a given course for a specific period of time, and orders the results in decreasing number of hits.
SELECT u.firstname, u.lastname, COUNT(*) AS hits FROM prefix_log l LEFT JOIN prefix_user u ON l.userid=u.id WHERE `course` = 1693 AND `time` BETWEEN 1363795201 AND 1366819199 AND `userid` != 782 GROUP BY `userid` ORDER BY hits DESC
To customise, modify ‘course’ and ‘userid’, as explained for the query above. You can also change the Epoch values to adjust the period of time you are interested in.
Number of hits per student, per day
This query calculates the number of hits students have performed each day in a given course. The results are ordered chronologically.
SELECT u.id, u.firstname, u.lastname, DATE(FROM_UNIXTIME(`time`)) AS `Date`, COUNT(*) AS hits FROM prefix_log l LEFT JOIN prefix_user u ON l.userid=u.id WHERE `course` = 1693 AND `userid` != 782 GROUP BY `userid`, DATE(FROM_UNIXTIME(`time`)) ORDER BY `Date`
Number of visits to resources/activities
Calculates how many times all resources & activities have been visited in a given course for a specific period of time.
SELECT COUNT(cmid) AS hits, cmid, module FROM prefix_log WHERE `time` BETWEEN 1363795201 AND 1366819199 AND `userid` != 782 AND `course` = 1693 GROUP BY `cmid` ORDER BY cmid ASC
To customise, see ‘Number of hits per student’ query above.
Number of visitors to resources/activities
Works out how many students have visited resources & activities at least once in a given course, for a specific period of time.
SELECT COUNT(Distinct userid) AS hits, cmid, module FROM prefix_log WHERE `time` BETWEEN 1363795201 AND 1366819199 AND `userid` != 782 AND `course` = 1693 GROUP BY `cmid` ORDER BY cmid ASC
To customise, see ‘Number of hits per student’ query above.
Number of hits during lessons
This query works out the number of hits performed during lesson time. I can then subtract it from the ‘Hits in a course’ query to find out the ratio of hits in/outside lessons.
SELECT course, COUNT(*) AS hits FROM `prefix_log` WHERE ( (`time` BETWEEN 1363923300 AND 1363926600) OR (`time` BETWEEN 1364190300 AND 1364196900) OR (`time` BETWEEN 1364362800 AND 1364366400) OR (`time` BETWEEN 1365564000 AND 1365567000) OR (`time` BETWEEN 1365658800 AND 1365665700) OR (`time` BETWEEN 1366004400 AND 1366008000) OR (`time` BETWEEN 1366255800 AND 1366259400) OR (`time` BETWEEN 1366350000 AND 1366356900) OR (`time` BETWEEN 1366695600 AND 1366699200) OR (`time` BETWEEN 1366687800 AND 1366691400) ) AND `userid` != 782 AND `course` = 1693 GROUP BY `course`
Note: this query could possibly be simplified if your lesson times are always the same every week. My timetable is an absolute mess, with a 6 day cycle with Wednesdays following a different schedule.
Badges awarded to each student
This query works out what badges have been unlocked by every student in a given course. As it is a rather long query, I have attached it to a text file, that you can download here. Here is a simplified sample:
SELECT userid, case BADGE_1 when 3 then 'y' else '-' END as BADGE_1, case BADGE_2 when 1 then 'y' else '-' END as BADGE_2 from (SELECT userid, sum(col14415) + sum(col14416) + sum(col14417) as BADGE_1, sum(col14413) as BADGE_2 from (SELECT userid, case coursemoduleid when 14413 then 1 else 0 end as col14413, case coursemoduleid when 14415 then 1 else 0 end as col14415, case coursemoduleid when 14416 then 1 else 0 end as col14416, case coursemoduleid when 14417 then 1 else 0 end as col14417 FROM mdl_course_modules_completion WHERE completionstate = 1 GROUP BY userid , coursemoduleid) t GROUP BY userid) t2 WHERE userid BETWEEN 1223 AND 1243 ORDER BY userid ASC
I might create a short video showing how to customise this query, if there is interest. However, if you take a look at the attached file along with this sample, you should be able to work out what to do to make it suit your needs.
Note: It takes a long time to set up.
Activity has been completed, but student has not marked it complete
This query finds all of the resources & activities that have been visited by a student, but have not been marked as actually completed by the student. A student might not have marked the resource/activity as completed for several reasons, such as forgetting to do it, not feeling confident he/she has understood the contents and would like to revisit, etc. The query creates a clickable link to the resource to help you judge the possible reason(s).
SELECT u.firstname, u.lastname, u.institution AS form, u.department AS house, l.module AS type, l.course, CONCAT('<a href="%%WWWROOT%%/mod/',l.module,'/',l.url,'" target="_new">',l.cmid,'</a>') AS module FROM prefix_log l LEFT JOIN prefix_user u ON u.id = l.userid WHERE NOT EXISTS ( SELECT NULL FROM prefix_course_modules_completion c WHERE l.cmid = c.coursemoduleid AND c.userid = l.userid ) AND l.course = 1693 AND l.userid !=782 AND l.cmid !=0 AND l.module !="data" AND l.module !="checklist" GROUP BY l.userid ORDER BY l.course ASC, u.firstname
Due to the way I have set up the conditions, it is possible that a student has visited the ‘database’ and/or the ‘checklist’ activities and the system has not yet marked it as completed. For this reason, I have removed it from the list of modules to be evaluated (e.g. l.module !=”data”)
Activity has not been visited, but student has marked it done
This query works out the opposite of the one above. Sometimes students might mark an activity as completed, even though they have not visited it. This query works out just that.
SELECT u.firstname, u.lastname, u.institution AS form, u.department AS house, concat('<a href="%%WWWROOT%%/mod/',p.name,'/view.php?id=',c.coursemoduleid,'" target="_new">',c.coursemoduleid,'</a>') AS module, p.name AS type, m.course FROM prefix_course_modules_completion c LEFT JOIN prefix_course_modules m ON c.coursemoduleid = m.id LEFT JOIN prefix_user u ON c.userid = u.id LEFT JOIN prefix_modules p ON m.module = p.id WHERE NOT EXISTS ( SELECT NULL FROM prefix_log l WHERE c.coursemoduleid = l.cmid AND c.userid = l.userid ) AND m.module != 9 AND m.course = 1693 AND c.completionstate = 1 ORDER BY c.userid
I have removed the ‘label’ module type from this query as it may cause problems, as a label can be marked as complete but it is impossible for a student to visit it (i.e. click on it and open a specific page for it). I probably should also be removing the ‘turnitintool’ type as well but have not done it yet.
You can find more useful Moodle SQL queries here. Are there any queries you would like to share?