The solution to Homework 5.3 (Hands-On) M101JS: MongoDB for Node.js DevelopersWho’s the easiest grader on campus?
A set of grades are loaded into the grades collection. The documents look like this:
{ "_id" : ObjectId("50b59cd75bed76f46522c392"), "student_id" : 10, "class_id" : 5, "scores" : [ { "type" : "exam", "score" : 69.17634380939022 }, { "type" : "quiz", "score" : 61.20182926719762 }, { "type" : "homework", "score" : 73.3293624199466 }, { "type" : "homework", "score" : 15.206314042622903 }, { "type" : "homework", "score" : 36.75297723087603 }, { "type" : "homework", "score" : 64.42913107330241 } ] }
There are documents for each student (student_id) across a variety of classes (class_id). Note that not all students in the same class have the same exact number of assessments. Some students have three homework assignments, etc.
Your task is to calculate the class with the best average student performance. This involves calculating an average for each student in each class of all non-quiz assessments and then averaging those numbers to get a class average. To be clear, each student’s average includes only exams and homework grades. Don’t include their quiz scores in the calculation.
What is the class_id which has the highest average student performance?
Hint/Strategy: You need to group twice to solve this problem. You must figure out the GPA that each student has achieved in a class and then average those numbers to get a class average. After that, you just need to sort. The class with the lowest average is the class with class_id=2. Those students achieved a class average of 37.6
If you prefer, you may download the handout and perform your analysis on your machine with
mongoimport -d test -c grades --drop grades.json
Solution:
Run the following SQL and the first id that you will get be the solution.
db.grades.aggregate([ { $unwind: "$scores" }, { $match: { $or: [ {"scores.type": "homework"}, {"scores.type":"exam"} ] } }, { $group: { _id: { 'student_id': "$student_id", 'class_id': "$class_id" }, avg: { $avg: "$scores.score" } } }, { $group: { _id: "$_id.class_id", class_avg: { $avg: "$avg" } } }, { $sort: { 'class_avg': -1 } } ])
Following is the result that I got:
So the answer is: