Moodle SQL number of students

Question

I am not really an export in SQL, I found this code so far, which lists the number of students (who enrolled) grouped by courses. I want to have a Start date and and End date, so what I want is I set the start date 2022/04/05 and the end date 2022/04/09 and the code should list the number of students who enrolled in the courses in this specific range. I found this code so far.

SELECT c.fullname, COUNT(ue.id) AS Enroled

FROM prefix_course AS c 

JOIN prefix_enrol AS en ON en.courseid = c.id

JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id

GROUP BY c.id

ORDER BY c.fullname

I know that somehow I have to add %starttime% and %endtime% to be able to choose two dates but I don't know how.

Answer 1

SELECT c.id, c.fullname, COUNT(*)
FROM mdl_course c
JOIN (
    SELECT DISTINCT e.courseid, ue.userid /* Distinct because one user can be enrolled multiple times */
    FROM mdl_enrol e
    JOIN mdl_user_enrolments ue ON ue.enrolid = e.id AND ue.timestart BETWEEN :starttime AND :endtime
) students ON students.courseid = c.id
GROUP BY c.id, c.fullname

The time enrolled is stored in mdl_user_enrolments.timestart

There can be multiple enrolment methods on a course and it is possible that a student is enrolled more than once on a course using different methods. So you'll need to us a distinct join.

You might also want do add some additional filters. eg:

Check the user hasn't been suspended

JOIN mdl_user u ON u.id = ue.userid AND u.suspended = 0

The user enrolment is active

AND ue.status = 0 /* ENROL_USER_ACTIVE */

The Enrolment method is active

AND e.status = 0 /* ENROL_INSTANCE_ENABLED */