Useful SQL scripts

If you are a Moodle site administrator, then probably this is the best collection of SQL queries which can be utilized to make custom reports on your Moodle site.

• https://docs.moodle.org/28/en/ad-hoc_contributed_reports
• https://docs.moodle.org/22/en/Custom_SQL_queries_report
• https://moodle.org/mod/forum/discuss.php?d=153059

Few of the useful SQl queries are:

Student (user) COUNT in each Course

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',course.id,'">',course.fullname,'</a>') AS Course
,concat('<a target="_new" href="%%WWWROOT%%/user/index.php?contextid=',context.id,'">Show users</a>') AS Users
, COUNT(course.id) AS Students
FROM prefix_role_assignments AS asg
JOIN prefix_context AS context ON asg.contextid = context.id AND context.contextlevel = 50
JOIN prefix_user AS USER ON USER.id = asg.userid
JOIN prefix_course AS course ON context.instanceid = course.id
WHERE asg.roleid = 5
# AND course.fullname LIKE '%2013%'
GROUP BY course.id
ORDER BY COUNT(course.id) DESC

Most popular ACTIVITY

SELECT COUNT(l.id) hits, module
FROM prefix_log l
WHERE module != 'login' AND module != 'course' AND module != 'role'
GROUP BY module
ORDER BY hits DESC

Courses without Teachers

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,(SELECT COUNT( ra.userid ) AS Users FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 3 AND ctx.instanceid = c.id) AS Teachers
FROM prefix_course AS c
ORDER BY Teachers ASC

You can also use the popular report plugins like:
Ad-hoc database queries : https://moodle.org/plugins/view/report_customsql
Configurable Reports : https://moodle.org/plugins/view/block_configurable_reports