I am trying to craft reports using the "Ad Hoc database queries" in Moodle 3.3.
Although I found the schema it is partial, and missing several tables. I have looked across all the documentation and have a hard time locating anything that provides clear picture how all the tables interrelate.
Where could I get more information on how to craft ad hoc SQL statements?
For example - I am trying to write a query that lists all courses within a date range, the count of enrolled students and the count of passing student in each.
I can get the number of students enrolled, but how do I combine it with
SELECT prefix_course.shortname, prefix_course.id, prefix_course.startdate AS Start_Date, prefix_course.enddate AS End_Date, COUNT(prefix_user_enrolments.id) AS Enrolled FROM prefix_course JOIN prefix_enrol ON prefix_enrol.courseid = prefix_course.id JOIN prefix_user_enrolments ON prefix_user_enrolments.enrolid = prefix_enrol.id WHERE prefix_course.startdate >= :startdate GROUP BY prefix_course.id ORDER BY prefix_course.startdate, prefix_course.shortname