How do you determine the BEST STUDENT of a programme when the time is right?

Frankie's the name, Moodle's my game!
How do you determine the BEST STUDENT of a programme when the time is right?

Hi All

After N number of terms (semesters) I am trying to figure out a way to use Moodle's gradebook to determine the OVERALL BEST STUDENT of a programme. E.g., a particular cohort started their Diploma in April 2016. So in June 2018, they are due to graduate. Admin needs to quickly and accurately determine (not guess!) who is the overall best student based on the marks recorded in Moodle's Gradebook.

Perhaps a high level list like the one below that is sorted on average Course Final Grade score:

Name            Average Course Final Grade score for all courses enrolled
Alex Lion      90%
Tony Tiger   80%
Daisy Duck  70%

If nothing like the above is immediately or readily available, I would like to ask how do you determine, at the end of a programme, the overall best student?

Frankie Kam

Frankie's the name, Moodle's my game!
Re: How do you determine the BEST STUDENT of a programme when the time is right?

I am answering my own question after eight days. Haha. 

I solved this. The trick is to use the Configurable reports block (plugin).

For your convenience, I reproduce the SQL Query code in the text below:

SELECT AS courseid, prefix_course.shortname AS Course_ShortName, AS studentid, prefix_user.firstname AS FirstName, prefix_user.lastname AS LastName, prefix_grade_grades.finalgrade AS coursetotalgrade
FROM prefix_grade_grades
JOIN prefix_user ON prefix_grade_grades.userid =
JOIN prefix_grade_items ON prefix_grade_grades.itemid =
JOIN prefix_course ON prefix_grade_items.courseid =
WHERE prefix_grade_items.itemtype = 'course'AND
prefix_user.lastname='GIJB Jan 2016'
ORDER BY prefix_course.idnumber, prefix_user.username, prefix_grade_items.itemname, prefix_grade_grades.timemodified

Note: replace  the line prefix_user.lastname='GIJB Jan 2016' with your own values between the apostrophes.

Clicking the "View report" tab (which is to the immediate left of "Custom SQL") of the block tab yields this output for me:

I then save the output as an Excel export file. The rest is manual work on the Excel to give me enough information as to who is the top scorer.

Hope this helps someone.