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;