This version has a column for what the grade letter would be using the site default grade scale. You can tell the site default is used when the overridden column is null. -Ben
------
select
c.id courseid,
c.fullname course,
u.id userid,
concat(u.lastname, ', ', u.firstname) user,
#percent rounded to 2 decimal places
round(g.finalgrade,2) percent,
#letter grade, uses any custom letters for the course
(SELECT l.letter FROM mdl_grade_letters l
join mdl_context x on l.contextid = x.id
WHERE x.contextlevel = 50
and x.instanceid in (c.id, 0) and l.lowerboundary <= g.finalgrade
ORDER BY x.id desc, lowerboundary desc limit 1) overridden_grade_scale,
(SELECT l.letter FROM mdl_grade_letters l
WHERE l.contextid = 1
and l.lowerboundary <= g.finalgrade
ORDER BY l.lowerboundary desc limit 1) default_grade_scale,
#this is the grade feedback from the gradebook, you could see this in SingleView
g.feedback comments
from mdl_user AS u
join mdl_grade_grades g on g.userid = u.id
join mdl_grade_items gi on gi.id = g.itemid
JOIN mdl_course c on c.id = gi.courseid
#Join relationship for student role in the course
JOIN mdl_enrol AS en ON en.courseid = c.id
JOIN mdl_user_enrolments AS ue ON ue.enrolid = en.id and ue.userid = u.id
JOIN mdl_context AS ctx ON c.id = ctx.instanceid
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id and ra.userid = u.id
JOIN mdl_role r on ra.roleid = r.id
where gi.itemtype = 'course'
#filter required for student role
AND r.shortname = 'student'
#specific userid
and u.id = 86
#specific courseid
and gi.courseid = 815
