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.