$avg accumulator in MongoDB - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Calculate average score per student

Given the following documents in the students collection:

{ "name": "Alice", "scores": [80, 90, 100] }
{ "name": "Bob", "scores": [70, 85, 90] }
{ "name": "Charlie", "scores": [60, 75, 85] }

Which aggregation pipeline will correctly calculate the average score for each student?

MongoDB

db.students.aggregate([
  { $project: { name: 1, avgScore: { $avg: "$scores" } } }
])

A [ { "$project": { "name": 1, "avgScore": { "$avg": "$scores" } } } ]

B [ { "$group": { "_id": "$name", "avgScore": { "$avg": "$scores" } } } ]

C [ { "$project": { "name": 1, "avgScore": { "$avg": "$scores.score" } } } ]

D [ { "$group": { "_id": "$name", "avgScore": { "$avg": "$scores.score" } } } ]

💡 Hint

Use $project to calculate average on an array field within each document.

The $avg accumulator can calculate the average of values in an array when used inside $project. Using $group here is incorrect because scores is an array inside each document, not a field to group by.

❓ query_result

intermediate

Average price per category

Consider a products collection with documents like:

{ "category": "Books", "price": 15 }
{ "category": "Books", "price": 25 }
{ "category": "Electronics", "price": 100 }

Which aggregation pipeline will return the average price for each category?

MongoDB

db.products.aggregate([
  { $group: { _id: "$category", avgPrice: { $avg: "$price" } } }
])

A [ { "$group": { "_id": "$category", "avgPrice": { "$avg": "$category.price" } } } ]

B [ { "$project": { "category": 1, "avgPrice": { "$avg": "$price" } } } ]

C [ { "$group": { "_id": "$category", "avgPrice": { "$avg": "$price" } } } ]

D [ { "$match": { "price": { "$exists": true } } }, { "$group": { "_id": "$category", "avgPrice": { "$avg": "$price" } } } ]

💡 Hint

Use $group to aggregate by category and calculate average price.

The $group stage groups documents by category and calculates the average price. $project cannot aggregate across documents. Option C adds an unnecessary $match but does not change correctness.

📝 Syntax

advanced

Identify the syntax error in $avg usage

Which of the following aggregation stages will cause a syntax error due to incorrect $avg usage?

A { $project: { average: { $avg: "$values" } } }

B { $group: { _id: "$type", average: { $avg: "$value" } } }

C { $group: { _id: "$type", average: { $avg: ["$value1", "$value2"] } } }

D { $group: { _id: "$type", average: { $avg: "$value", $sum: 1 } } }

💡 Hint

Check if the accumulator object contains invalid extra fields.

Option D incorrectly mixes $avg and $sum inside the same object, which is invalid syntax. Each accumulator must be a single operator.

❓ optimization

advanced

Optimize average calculation on large dataset

You want to calculate the average rating from a large reviews collection. Which pipeline is more efficient?

A [ { $match: { rating: { $exists: true } } }, { $group: { _id: null, avgRating: { $avg: "$rating" } } } ]

B [ { $group: { _id: null, avgRating: { $avg: "$rating" } } } ]

C [ { $project: { rating: 1 } }, { $group: { _id: null, avgRating: { $avg: "$rating" } } } ]

D [ { $group: { _id: "$userId", avgRating: { $avg: "$rating" } } } ]

💡 Hint

Filtering out documents without rating before grouping can improve performance.

Option A filters documents to only those with a rating before grouping, reducing data processed in $group. Option A processes all documents, including those without rating. Option A projects but does not filter. Option A groups by userId, not overall average.

🧠 Conceptual

expert

Understanding $avg behavior with missing or null values

What is the result of the following aggregation on this sales collection?

Documents:

{ "amount": 100 }
{ "amount": null }
{ }

Pipeline:

[ { $group: { _id: null, averageAmount: { $avg: "$amount" } } } ]

What will averageAmount be?

MongoDB

db.sales.aggregate([{ $group: { _id: null, averageAmount: { $avg: "$amount" } } }])

A null

B 100

C 50

D 0

💡 Hint

Consider how $avg treats null or missing fields in aggregation.

$avg ignores null and missing values when calculating the average. Only the value 100 is considered, so the average is 100.

Practice

1. What does the $avg accumulator do in a MongoDB aggregation pipeline?

easy

A. Calculates the average value of numeric fields in grouped documents

B. Counts the total number of documents in a collection

C. Finds the maximum value in a group of documents

D. Sorts documents in ascending order

Solution

1. Step 1: Understand the purpose of $avg

   The $avg accumulator is designed to calculate the mean (average) of numeric values within grouped documents in an aggregation pipeline.

2. Step 2: Compare with other accumulators

   Other accumulators like $sum, $max, and $count perform different tasks, so $avg specifically calculates averages.

3. Final Answer:

   Calculates the average value of numeric fields in grouped documents -> Option A

4. Quick Check:

   $avg = average calculation [OK]

Hint: Remember: $avg means average, not count or max [OK]

Common Mistakes:

• Confusing $avg with $sum or $count
• Thinking $avg sorts data
• Assuming $avg works outside aggregation

2. Which of the following is the correct syntax to calculate the average of the field score in a MongoDB aggregation $group stage?

easy

A. { $group: { _id: null, avgScore: { $average: "$score" } } }

B. { $group: { _id: null, avgScore: { $avg: "$score" } } }

C. { $group: { _id: null, avgScore: { avg: "$score" } } }

D. { $group: { _id: null, avgScore: { $sum: "$score" } } }

Solution

1. Step 1: Identify the correct accumulator name

   The correct MongoDB accumulator for average is $avg, not $average or avg.

2. Step 2: Check the syntax structure

   The syntax requires the field name as a string with a $ prefix inside $avg, and the whole inside a $group stage with an _id.

3. Final Answer:

   { $group: { _id: null, avgScore: { $avg: "$score" } } } -> Option B

4. Quick Check:

   Correct syntax uses $avg with field string [OK]

Hint: Use $avg exactly, with field as "$fieldName" [OK]

Common Mistakes:

• Using $average instead of $avg
• Omitting quotes around field name
• Using $sum instead of $avg

3. Given the collection students with documents:

{ "name": "Alice", "score": 80 }, { "name": "Bob", "score": 90 }, { "name": "Charlie", "score": 70 }

What will be the output of this aggregation?

[{ $group: { _id: null, averageScore: { $avg: "$score" } } }]

medium

A. [{ "_id": null, "averageScore": 240 }]

B. [{ "_id": null, "averageScore": 70 }]

C. [{ "_id": null, "averageScore": 80 }]

D. [{ "_id": null, "averageScore": 90 }]

Solution

1. Step 1: Calculate the sum of scores

   Sum = 80 + 90 + 70 = 240.

2. Step 2: Calculate the average

   Average = 240 / 3 = 80.

3. Final Answer:

   [{ "_id": null, "averageScore": 80 }] -> Option C

4. Quick Check:

   Average of 80, 90, 70 = 80 [OK]

Hint: Add all scores, divide by count for average [OK]

Common Mistakes:

• Using sum instead of average
• Picking minimum or maximum score
• Forgetting to divide by number of documents

4. Consider this aggregation pipeline:

[{ $group: { _id: "$class", avgScore: { $avg: score } } }]

What is the error in this pipeline?

medium

A. Cannot use $avg inside $group

B. Missing $match stage before $group

C. The _id field cannot be "$class"

D. Field name 'score' should be a string with $ prefix: "$score"

Solution

1. Step 1: Check the field reference inside $avg

   The field name must be a string with a $ prefix, so it should be "$score", not score.

2. Step 2: Validate other parts of the pipeline

   Using "$class" as _id is valid, and $avg is allowed inside $group. $match is optional.

3. Final Answer:

   Field name 'score' should be a string with $ prefix: "$score" -> Option D

4. Quick Check:

   Field names in $avg need "$field" format [OK]

Hint: Always quote field names with $ in accumulators [OK]

Common Mistakes:

• Using unquoted field names inside $avg
• Thinking $match is mandatory before $group
• Believing _id cannot be a field name

5. You have a collection sales with documents:

{ "region": "North", "amount": 100 }, { "region": "South", "amount": 200 }, { "region": "North", "amount": 300 }, { "region": "South", "amount": 400 }

Write an aggregation pipeline to find the average sales amount per region and identify the region with the highest average sales.

hard

A. [{ $group: { _id: "$region", avgAmount: { $avg: "$amount" } } }, { $sort: { avgAmount: -1 } }, { $limit: 1 }]

B. [{ $group: { _id: "$region", avgAmount: { $sum: "$amount" } } }, { $sort: { avgAmount: 1 } }]

C. [{ $group: { _id: "$region", avgAmount: { $avg: "$amount" } } }, { $match: { avgAmount: { $gt: 300 } } }]

D. [{ $group: { _id: "$region", avgAmount: { $avg: "$amount" } } }, { $sort: { avgAmount: 1 } }, { $limit: 1 }]

Solution

1. Step 1: Group by region and calculate average

   Use $group with _id: "$region" and $avg: "$amount" to get average sales per region.

2. Step 2: Sort descending by average and limit to top

   Sort by avgAmount descending (-1) to get highest average first, then limit to 1 document.

3. Final Answer:

   [{ $group: { _id: "$region", avgAmount: { $avg: "$amount" } } }, { $sort: { avgAmount: -1 } }, { $limit: 1 }] -> Option A

4. Quick Check:

   Group, sort descending, limit 1 = highest average [OK]

Hint: Group by region, avg, sort desc, limit 1 for highest [OK]

Common Mistakes:

• Using $sum instead of $avg for average
• Sorting ascending to get highest average
• Not limiting results to top 1