Following from Luis, the examples I would first look at are
"Grades for all students in all courses" and
"List Students with enrollment and completion dates in current course"
just to understand which tables might be most relevent.
I am fairly new to SQL myself, but some starting tips are:
- Take a short course - it will help immeasurably to make sense of the contributed reports! i took this 90 min one: www.skillshare.com/classes/SQL-Master-SQL-Database-Queries-in-Just-90-Mins/1192226128
- Understand tables - information is sorted into different tables, and all moodle tables start with "prefix_"
you can search and view the tables and relationships here https://www.examulator.com/er/output/index.html
- Bookmark this reference to all the variables and filters you can use in the plug-in(it is a duplication of the information for configurable reports I just can't find it)https://opensourceelearning.blogspot.com/2015/08/moodle-configurable-reports-plugin-all.html
***
this has not been tested - but an example combining the two tables above (lines beginning with # are comments and not part of the code (you can delete). You will also need to put more constraints in if you have a large number of courses
Also note that I am not entirely sure which fields in the course completed table are what they appear to be. my advice is to get enrolled and start dates, and check them against the results here
#replaced u.username with first and last name, email
#added time enrolled and time started from the course completion table
SELECT
u.lastname, u.firstname, u.email,
c.shortname AS "Course", cc.timeenrolled AS enrolled,
# added the percentage sign to the grade
CONCAT(ROUND(gg.finalgrade),'%') AS "Grade",
#added enrolment date from course completions table - (note time started will return a generic date 1970/1/1 when there is no data, so have removed)
DATE_FORMAT(FROM_UNIXTIME(cc.timestarted), '%Y-%m-%d') AS "Enrolled",
DATE_FORMAT(FROM_UNIXTIME(gg.timemodified), '%Y-%m-%d') AS "Date"
FROM prefix_course AS c
#added course completion table to get enrol and start dates
JOIN prefix_course_completions AS cc ON c.id = cc.course
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
WHERE gi.courseid = c.id
#the courseid line below returns values for the course you are currently in (viewing the report from), it can help if the query is taking too long)
AND c.id = %%COURSEID%%
AND gi.itemtype = 'course'
# students only role id is 5
AND ra.roleid = 5
ORDER BY u.lastname, c.shortname
