Solution to Homework 5.3 (Hands On) M101JS: MongoDB for Node.js Developers

Who’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:

hw5.3answer2

So the answer is:

 

hw5.3answer

LEAVE A REPLY

Please enter your comment!
Please enter your name here