I've come to compile a list of queries for Moodle, aggregrated from various sources on the internet. Credits to the original authors, whomever they be. (sorry for not keeping track)
# Top 20 courses in terms of student enrolment for specfic year or term (assuming year code is built in to courses)
SELECT DISTINCT c.fullname, c.shortname, count( usr.id )
FROM mdl_course c
INNER JOIN mdl_context cx ON c.id = cx.instanceid
AND cx.contextlevel = '50'
INNER JOIN mdl_role_assignments ra ON cx.id = ra.contextid
INNER JOIN mdl_role r ON ra.roleid = r.id
INNER JOIN mdl_user usr ON ra.userid = usr.id
WHERE r.name = 'Student' and c.idnumber like '%\_2010%'
GROUP BY c.fullname
ORDER BY count( usr.id ) DESC
LIMIT 0 , 20
# List of Lecturers and how much courses they teach
SELECT user.firstname, user.lastname, user.email, count( role.id ) AS courses
FROM mdl_course course
JOIN mdl_context context ON ( course.id = context.instanceid
AND context.contextlevel =50 )
JOIN mdl_role_assignments assign ON ( context.id = assign.contextid )
JOIN mdl_role role ON ( assign.roleid = role.id )
JOIN mdl_user user ON ( assign.userid = user.id )
WHERE role.shortname = 'editingteacher'
GROUP BY user.firstname, user.lastname, user.email
ORDER BY user.lastname, user.firstname
# Courses with MMS streaming media for specfic year or term (assuming year code is built in to courses)
SELECT DISTINCT c.shortname
FROM mdl_resource r, mdl_course c
WHERE r.reference LIKE '%mms://%'
AND c.shortname LIKE '%\_2010%'
AND r.course = c.id
#List of courses and number of activities for specfic year or term (assuming year code is built in to courses) (sorted by total num_activities )
SELECT m.id, m.`shortname` , m.fullname, cCount.totalcount
FROM mdl_course m
LEFT JOIN (
SELECT courseCount.course, sum( courseCount.subcount ) AS totalcount
FROM (
SELECT course, count( * ) AS subcount
FROM mdl_resource
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_quiz
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_assignment
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_survey
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_label
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_glossary
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_wiki
GROUP BY course) AS courseCount
GROUP BY courseCount.course) AS cCount ON cCount.course = m.id
WHERE m.shortname LIKE '%\_2010%'
ORDER BY cCount.totalcount ASC
