Moodle Queries (SQL) Series #1
Student aktif per course
Banyaknya student aktif per course pada bulan Oktober 2025, hanya untuk course dengan lebih dari 100 student aktif.
SELECT
c.id AS courseid,
c.fullname AS coursename,
COUNT(DISTINCT l.userid) AS active_students
FROM mdl_logstore_standard_log l
JOIN mdl_course c ON c.id = l.courseid
JOIN mdl_role_assignments ra ON ra.userid = l.userid
JOIN mdl_context ctx ON ctx.id = ra.contextid
WHERE
ctx.contextlevel = 50 -- contextlevel 50 = course
AND ctx.instanceid = c.id
AND ra.roleid = 5 -- roleid 5 = student
AND l.timecreated BETWEEN UNIX_TIMESTAMP('2025-10-01 00:00:00')
AND UNIX_TIMESTAMP('2025-10-31 23:59:59')
GROUP BY c.id, c.fullname
HAVING active_students > 100
ORDER BY active_students DESC;
Active users menggunakan tabel mdl_users_lastaccess
“active” = enrolled + accessed the course recently
SELECT
c.id AS courseid,
c.fullname AS coursename,
COUNT(DISTINCT CASE
WHEN ula.timeaccess BETWEEN UNIX_TIMESTAMP('2025-10-01 00:00:00')
AND UNIX_TIMESTAMP('2025-10-31 23:59:59')
THEN ula.userid END) AS active_students,
COUNT(DISTINCT ra.userid) AS total_students,
ROUND(
(COUNT(DISTINCT CASE
WHEN ula.timeaccess BETWEEN UNIX_TIMESTAMP('2025-10-01 00:00:00')
AND UNIX_TIMESTAMP('2025-10-31 23:59:59')
THEN ula.userid END)
/ COUNT(DISTINCT ra.userid)) * 100, 2
) AS active_percentage
FROM mdl_course c
JOIN mdl_context ctx
ON ctx.contextlevel = 50 AND ctx.instanceid = c.id
JOIN mdl_role_assignments ra
ON ra.contextid = ctx.id AND ra.roleid = 5 -- 5 = student
LEFT JOIN mdl_user_lastaccess ula
ON ula.courseid = c.id AND ula.userid = ra.userid
GROUP BY c.id, c.fullname
HAVING active_students > 100
ORDER BY active_students DESC;