Aggregate and annotate methods in Django - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ component_behavior

intermediate

What does this Django ORM query output?

Given the following Django model and query, what will be the output of print(result)?

Django

from django.db import models
from django.db.models import Count

class Book(models.Model):
    title = models.CharField(max_length=100)

class Author(models.Model):
    name = models.CharField(max_length=100)
    books = models.ManyToManyField(Book)

# Query
result = Author.objects.annotate(num_books=Count('books')).values('name', 'num_books')

A [{'name': 'Alice', 'num_books': 3}, {'name': 'Bob', 'num_books': 0}]

B [{'name': 'Alice', 'num_books': 0}, {'name': 'Bob', 'num_books': 3}]

C [{'name': 'Alice', 'num_books': None}, {'name': 'Bob', 'num_books': None}]

D Raises a TypeError because Count cannot be used with ManyToManyField

💡 Hint

Think about how annotate with Count works on ManyToMany relationships.

The annotate method adds a new field num_books to each Author, counting how many related Book objects they have. Authors with no books get 0.

📝 Syntax

intermediate

Which option correctly annotates the average rating of books?

Given a Book model with a rating field, which Django ORM query correctly annotates each author with the average rating of their books?

Django

from django.db.models import Avg

# Choose the correct query:

A Author.objects.annotate(avg_rating=Avg('books__rating'))

B Author.objects.annotate(avg_rating=Avg('rating'))

C Author.objects.aggregate(avg_rating=Avg('books__rating'))

D Author.objects.annotate(avg_rating=Avg('book__rating'))

💡 Hint

Remember to use the related field name for the join.

To annotate authors with the average rating of their related books, you use Avg('books__rating'). Option A misses the relation, C uses aggregate which returns a single value, and D has a typo in the related field name.

🔧 Debug

advanced

Why does this annotation produce incorrect counts?

Consider this query:

Author.objects.annotate(num_books=Count('books')).filter(num_books__gt=1)

It returns authors with 1 book instead of more than 1. Why?

A Because Count counts duplicates due to joins; distinct=True is needed

B Because filter should be applied before annotate, not after

C Because num_books is not a valid annotation name

D Because Count cannot be used with ManyToMany fields

💡 Hint

Think about SQL joins and duplicate rows.

When annotating with Count on ManyToMany fields, duplicates can inflate counts. Adding distinct=True fixes this.

🧠 Conceptual

advanced

What is the difference between aggregate() and annotate()?

Which statement best describes the difference between aggregate() and annotate() in Django ORM?

A <code>aggregate()</code> modifies each object; <code>annotate()</code> returns a single summary

B <code>aggregate()</code> filters the queryset; <code>annotate()</code> sorts the queryset

C <code>aggregate()</code> can only be used with Count; <code>annotate()</code> can use any function

D <code>aggregate()</code> returns a single dictionary with summary values; <code>annotate()</code> adds summary values to each item in the queryset

💡 Hint

Think about the shape of the result each method returns.

aggregate() returns a dictionary with overall summary values for the whole queryset. annotate() adds calculated fields to each item in the queryset.

❓ state_output

expert

What is the output of this complex annotation query?

Given these models and data, what will be the output of the query below? Models:

class Store(models.Model):
    name = models.CharField(max_length=100)

class Product(models.Model):
    store = models.ForeignKey(Store, on_delete=models.CASCADE)
    price = models.DecimalField(max_digits=6, decimal_places=2)

# Data:
# Store A has products priced 10.00, 20.00
# Store B has products priced 15.00

# Query:
from django.db.models import Max, F
result = Store.objects.annotate(
    max_price=Max('product__price'),
    discount_price=F('max_price') * 0.9
).values('name', 'max_price', 'discount_price').order_by('name')

Django

from django.db.models import Max, F

result = Store.objects.annotate(
    max_price=Max('product__price'),
    discount_price=F('max_price') * 0.9
).values('name', 'max_price', 'discount_price').order_by('name')

A [{'name': 'Store A', 'max_price': None, 'discount_price': None}, {'name': 'Store B', 'max_price': None, 'discount_price': None}]

B [{'name': 'Store A', 'max_price': 20.00, 'discount_price': 20.00}, {'name': 'Store B', 'max_price': 15.00, 'discount_price': 15.00}]

C [{'name': 'Store A', 'max_price': 20.00, 'discount_price': 18.00}, {'name': 'Store B', 'max_price': 15.00, 'discount_price': 13.50}]

D Raises a ValueError because F expressions cannot be multiplied by floats

💡 Hint

Check how F expressions work with arithmetic and aggregation.

The max_price is the highest product price per store. The discount_price multiplies that by 0.9 using an F expression, which Django supports with numeric literals.

Practice

1. What does the aggregate() method do in Django ORM?

easy

A. Returns a dictionary with summary values like count or sum for the whole queryset

B. Adds new fields to each object in the queryset with calculated values

C. Deletes all objects in the queryset

D. Filters the queryset based on aggregate conditions

Solution

1. Step 1: Understand aggregate() purpose

   The aggregate() method calculates summary values like count, sum, or average for the entire queryset.

2. Step 2: Compare with annotate()

   Unlike annotate(), which adds fields to each object, aggregate() returns a single dictionary summarizing the whole queryset.

3. Final Answer:

   Returns a dictionary with summary values like count or sum for the whole queryset -> Option A

4. Quick Check:

   aggregate() = summary dictionary [OK]

Hint: aggregate() summarizes whole queryset as dict [OK]

Common Mistakes:

• Confusing aggregate() with annotate()
• Thinking aggregate() modifies each object
• Assuming aggregate() filters data

2. Which of the following is the correct syntax to annotate each Author with the number of books they have written?

easy

A. Author.objects.annotate(Count('books'))

B. Author.objects.aggregate(book_count=Count('books'))

C. Author.objects.filter(book_count=Count('books'))

D. Author.objects.annotate(book_count=Count('books'))

Solution

1. Step 1: Identify annotate syntax

   The annotate() method requires a keyword argument to name the new field, e.g., book_count=Count('books').

2. Step 2: Check options

   Author.objects.annotate(book_count=Count('books')) correctly uses annotate(book_count=Count('books')). Author.objects.aggregate(book_count=Count('books')) uses aggregate() which returns a dict, not per object. Author.objects.filter(book_count=Count('books')) misuses filter(). Author.objects.annotate(Count('books')) misses the keyword argument.

3. Final Answer:

   Author.objects.annotate(book_count=Count('books')) -> Option D

4. Quick Check:

   annotate needs named field = Count(...) [OK]

Hint: annotate needs field_name=Count('related') [OK]

Common Mistakes:

• Using aggregate() instead of annotate()
• Not naming the annotation field
• Using filter() instead of annotate()

3. Given the model Book with a field price, what will this query return?

Book.objects.aggregate(total_price=Sum('price'))

medium

A. A dictionary with the sum of all book prices

B. A queryset of books with an extra field total_price

C. {'total_price': 0}

D. A list of prices of all books

Solution

1. Step 1: Understand aggregate() with Sum()

   The aggregate() method returns a dictionary with keys as the names given and values as the aggregate result. Here, it sums all price values.

2. Step 2: Interpret the output

   The result is a dictionary like {'total_price': sum_of_all_prices}, not a queryset or list.

3. Final Answer:

   A dictionary with the sum of all book prices -> Option A

4. Quick Check:

   aggregate() returns dict with sums [OK]

Hint: aggregate() returns dict, annotate() returns queryset [OK]

Common Mistakes:

• Expecting a queryset instead of a dict
• Confusing annotate() and aggregate() output
• Thinking it returns a list

4. What is wrong with this Django query?

Author.objects.annotate(Count('books'))

medium

A. Should use aggregate() instead of annotate()

B. Count() cannot be used inside annotate()

C. Missing a name for the annotation field

D. The model Author does not support annotate()

Solution

1. Step 1: Check annotate() usage

   The annotate() method requires named keyword arguments to assign the calculated value to a field.

2. Step 2: Identify the error

   Here, Count('books') is passed without a name, causing a syntax error.

3. Final Answer:

   Missing a name for the annotation field -> Option C

4. Quick Check:

   annotate() needs named fields [OK]

Hint: Always name your annotate fields like field=Count(...) [OK]

Common Mistakes:

• Forgetting to name the annotation field
• Using aggregate() when annotate() is needed
• Assuming annotate() can't use Count()

5. You want to list all Authors with their average book price, but only include authors who have written at least 3 books. Which query achieves this?

hard

A. Author.objects.aggregate(avg_price=Avg('books__price')).filter(book_count__gte=3)

B. Author.objects.annotate(avg_price=Avg('books__price'), book_count=Count('books')).filter(book_count__gte=3)

C. Author.objects.filter(Count('books') >= 3).annotate(avg_price=Avg('books__price'))

D. Author.objects.annotate(avg_price=Avg('books__price')).filter(book_count__gte=3)

Solution

1. Step 1: Annotate authors with average price and book count

   Use annotate() to add avg_price=Avg('books__price') and book_count=Count('books') fields to each Author.

2. Step 2: Filter authors with at least 3 books

   Apply filter(book_count__gte=3) to keep only authors with 3 or more books.

3. Step 3: Check options

   Author.objects.annotate(avg_price=Avg('books__price'), book_count=Count('books')).filter(book_count__gte=3) correctly chains annotate and filter. Author.objects.aggregate(avg_price=Avg('books__price')).filter(book_count__gte=3) wrongly uses aggregate() which returns a dict, so filter() fails. Author.objects.filter(Count('books') >= 3).annotate(avg_price=Avg('books__price')) misuses filter with Count(). Author.objects.annotate(avg_price=Avg('books__price')).filter(book_count__gte=3) filters on a field not annotated.

4. Final Answer:

   Author.objects.annotate(avg_price=Avg('books__price'), book_count=Count('books')).filter(book_count__gte=3) -> Option B

5. Quick Check:

   annotate then filter on annotated field [OK]

Hint: Annotate counts first, then filter on those counts [OK]

Common Mistakes:

• Using aggregate() instead of annotate() for filtering
• Filtering before annotating counts
• Not annotating book_count before filtering