Django ORM: top 5 customers by revenue in the last 30 days

Backend Live Coding November 13, 2026 python

Note: filters in the same filter() call apply to the same order.

python 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
# ======================================================================
# Q: Find the top 5 customers by revenue over the last 30 days (paid orders
# only).
#
# Note: filters in the same filter() call apply to the same order. One query
# with a JOIN and GROUP BY. I use unit_price from OrderItem (the price at the
# time of purchase), not the product's current price.
# ======================================================================

since = timezone.now() - timedelta(days=30)
revenue = ExpressionWrapper(F('orders__items__quantity') * F('orders__items__unit_price'),
                            output_field=DecimalField(max_digits=14, decimal_places=2))
top = (Customer.objects
       .filter(orders__status='paid', orders__created_at__gte=since)
       .annotate(revenue=Sum(revenue))
       .order_by('-revenue')[:5])

Related Posts

Python shallow copy vs deep copy
Python shallow copy vs deep copy
Python closures and late binding in lambdas
Python closures and late binding in lambdas
Storing heartbeats from 250,000 screens with Redis write coalescing
Storing heartbeats from 250,000 screens with Redis write coalescing
Writing a retry decorator with exponential backoff in Python
Writing a retry decorator with exponential backoff in Python
SQL: what a CTE is and how to find duplicate records
SQL: what a CTE is and how to find duplicate records
Threads vs processes vs asyncio: when to use each
Threads vs processes vs asyncio: when to use each