Django Module 4 – The ORM in depth: performance, integrity and PostgreSQL
Aggregation, annotations and expressions
Summarise Django data inside the database with aggregate(), annotate() and values(), and compute with F, Case, Subquery, Exists and window functions.
What you will learn
- Summarise rows with aggregate(), annotate() and values().annotate(), and predict the GROUP BY each one sends
- Count and sum only the rows you mean with filter=, distinct=True and default=, and spot joins that multiply rows
- Compute a value for every row with F(), Case and When, and ask about other rows with Subquery, OuterRef and Exists
- Rank rows inside each group with Window, Rank, DenseRank and RowNumber
- Replace a Python loop over rows with one annotated query, and measure the queries and the time it saves
Before you start
What you’ll learn · Before you start
What you will learn
- Summarise rows with aggregate(), annotate() and values().annotate(), and predict the GROUP BY each one sends
- Count and sum only the rows you mean with filter=, distinct=True and default=, and spot joins that multiply rows
- Compute a value for every row with F(), Case and When, and ask about other rows with Subquery, OuterRef and Exists
- Rank rows inside each group with Window, Rank, DenseRank and RowNumber
- Replace a Python loop over rows with one annotated query, and measure the queries and the time it saves
Before you start
On this page
The Nivas committee’s dues page adds up what the 407 flats of its 23 towers still owe, tower by tower. Written as a Python loop over the rows, the report sent 408 queries to the database; written as one annotated query, it sent 1, gave the same totals, and was more than 20 times faster, which this lesson’s measurement checks on every run.
You will summarise the society’s bills with aggregate() and annotate(), count and sum only the rows you mean,
catch the join that multiplies rows, compute a value for every row with F(), Case and When, ask about other rows
with Subquery and Exists, rank the flats of each tower with window functions, and measure what moving a loop into
the database saves. Every query below was recorded on Django 6.1.2 with SQLite 3.51.0.
aggregate() returns one dictionary, annotate() gives every row a value
The committee’s first questions are totals: how much was billed in all, how many bills each flat has, how much each tower was billed. Each one is an aggregate function: A function that turns many rows into one value, such as SUM, COUNT, AVG, MIN and MAX in SQL, or Sum, Count, Avg, Min and Max in Django. Over no rows every one of them but COUNT gives NULL. worked out by the database, and the call you choose decides the shape of the answer. The rows come from one file that every example imports first: three towers, seven flats, eighteen monthly bills and fourteen complaints.
"""The society the examples of this lesson summarise: three towers, seven flats, eighteen maintenance bills and
fourteen complaints, the same on every run.
Importing it starts Django, creates every table with migrate and adds the rows to an empty database; running it as a
script does the same. Tower C was handed over in October: its one flat has no bill yet. Money is in paise.
compact() prints a query's SQL shorter, so that it fits the page: without the double quotes and without the society_
prefix of the table names. The SQL that was sent is the same, with them.
"""
import os
import re
from datetime import date
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
import django # noqa: E402
django.setup()
from django.core.management import call_command # noqa: E402
from society.models import Bill, Complaint, Flat, Tower # noqa: E402
call_command('migrate', verbosity=0)
def compact(sql):
"""SELECT "society_bill"."month" FROM "society_bill" -> SELECT bill.month FROM bill"""
return re.sub(r'"society_(\w+)"', r'\1', sql).replace('"', '')
TODAY = date(2026, 10, 20) # the day the examples treat as today, so that "overdue" is the same on every run
MONTHS = [date(2026, 8, 1), date(2026, 9, 1), date(2026, 10, 1)] # each bill is due on the 25th of its month
FLATS = [('A', 101, 650), ('A', 102, 650), ('A', 1101, 1150),
('B', 101, 800), ('B', 1203, 1150), ('B', 1205, 1400),
('C', 101, 900)]
PAID = { # what each flat has paid of its August, September and October bills: 'all', 'none' or paise
'A-101': ['all', 'all', 'all'],
'A-102': ['all', 'none', 'none'],
'A-1101': ['all', 'all', 100_000],
'B-101': ['all', 100_000, 'none'],
'B-1203': ['all', 'all', 'all'],
'B-1205': ['none', 'none', 'none'],
}
COMPLAINTS = { # flat: (topic, status), …
'A-101': [('Lift stuck on floor 3', 'closed')],
'A-102': [('Seepage in the bathroom', 'open'), ('Doorbell wiring', 'open'), ('Corridor light off', 'open')],
'A-1101': [('Water pressure low', 'open'), ('Terrace door lock', 'open'), ('Lift noise', 'open')],
'B-1203': [('Parking slot taken', 'open'), ('Gym treadmill broken', 'closed')],
'B-1205': [('Seepage in the kitchen', 'open'), ('Water pressure low', 'open'), ('Pest control', 'open'),
('Intercom dead', 'open')],
'C-101': [('Water leak in the lobby', 'open')],
}
if not Tower.objects.exists():
towers = {name: Tower.objects.create(name=name, floors=floors) for name, floors in [('A', 14), ('B', 12), ('C', 8)]}
flats = {}
for tower, number, area in FLATS:
flats[f'{tower}-{number}'] = Flat.objects.create(tower=towers[tower], number=number, area_sqft=area)
for code, paid in PAID.items():
flat = flats[code]
amount = flat.area_sqft * 250 # 250 paise a square foot a month
for month, share in zip(MONTHS, paid):
Bill.objects.create(flat=flat, month=month, amount_paise=amount, due_on=month.replace(day=25),
paid_paise={'all': amount, 'none': 0}.get(share, share))
for code, items in COMPLAINTS.items():
for topic, status in items:
Complaint.objects.create(flat=flats[code], topic=topic, status=status)"""aggregate() and annotate() on the society's bills: what each returns, and the SQL it sent.
Run it next to manage.py. seed.py makes db.sqlite3 there with every table and the lesson's rows.
"""
import seed # noqa: F401 (starts Django, creates the tables, adds the rows)
from django.db import connection
from django.db.models import Count, Sum
from django.test.utils import CaptureQueriesContext
from seed import compact
from society.models import Bill, Flat, Tower
with CaptureQueriesContext(connection) as queries:
print('1. aggregate():', Bill.objects.aggregate(Sum('amount_paise')))
print('2. aggregate(), named:', Bill.objects.aggregate(billed=Sum('amount_paise'), paid=Sum('paid_paise'),
bills=Count('id')))
try:
Flat.objects.annotate(bills=Count('bills'))
except ValueError as error:
print(' annotate(bills=...) on flats:', error)
flats = Flat.objects.annotate(bill_count=Count('bills')).order_by('tower__name', 'number')
print('3. annotate() on flats:', [(f'{tower}-{number}', n) for tower, number, n in
flats.values_list('tower__name', 'number', 'bill_count')])
by_tower = Bill.objects.values('flat__tower__name').annotate(billed=Sum('amount_paise'))
print('4. values() then annotate():', list(by_tower.order_by('flat__tower__name')))
towers = Tower.objects.annotate(billed=Sum('flats__bills__amount_paise')).order_by('name')
print('5. annotate() on towers:', list(towers.values_list('name', 'billed')))
towers = Tower.objects.annotate(billed=Sum('flats__bills__amount_paise', default=0)).order_by('name')
print('6. the same with default=0:', list(towers.values_list('name', 'billed')))
print('7. values() then annotate(), ordered by flat number:',
[(row['flat__tower__name'], row['bills'])
for row in by_tower.annotate(bills=Count('id')).order_by('flat__number')])
print('The SQL of queries 3 to 7:')
for number, query in enumerate(queries, start=1):
if number >= 3:
print(f' {number}.', compact(query['sql']))Output
1. aggregate(): {'amount_paise__sum': 4350000}
2. aggregate(), named: {'billed': 4350000, 'paid': 2487500, 'bills': 18}
annotate(bills=...) on flats: The annotation 'bills' conflicts with a field on the model.
3. annotate() on flats: [('A-101', 3), ('A-102', 3), ('A-1101', 3), ('B-101', 3), ('B-1203', 3), ('B-1205', 3), ('C-101', 0)]
4. values() then annotate(): [{'flat__tower__name': 'A', 'billed': 1837500}, {'flat__tower__name': 'B', 'billed': 2512500}]
5. annotate() on towers: [('A', 1837500), ('B', 2512500), ('C', None)]
6. the same with default=0: [('A', 1837500), ('B', 2512500), ('C', 0)]
7. values() then annotate(), ordered by flat number: [('A', 3), ('B', 3), ('A', 3), ('A', 3), ('B', 3), ('B', 3)]
The SQL of queries 3 to 7:
3. SELECT tower.name AS tower__name, flat.number AS number, COUNT(bill.id) AS bill_count FROM flat LEFT OUTER JOIN bill ON (flat.id = bill.flat_id) INNER JOIN tower ON (flat.tower_id = tower.id) GROUP BY flat.id, flat.tower_id, 2, flat.area_sqft, 1 ORDER BY 1 ASC, 2 ASC
4. SELECT tower.name AS flat__tower__name, SUM(bill.amount_paise) AS billed FROM bill INNER JOIN flat ON (bill.flat_id = flat.id) INNER JOIN tower ON (flat.tower_id = tower.id) GROUP BY 1 ORDER BY 1 ASC
5. SELECT tower.name AS name, SUM(bill.amount_paise) AS billed FROM tower LEFT OUTER JOIN flat ON (tower.id = flat.tower_id) LEFT OUTER JOIN bill ON (flat.id = bill.flat_id) GROUP BY tower.id, 1, tower.floors ORDER BY 1 ASC
6. SELECT tower.name AS name, COALESCE(SUM(bill.amount_paise), 0) AS billed FROM tower LEFT OUTER JOIN flat ON (tower.id = flat.tower_id) LEFT OUTER JOIN bill ON (flat.id = bill.flat_id) GROUP BY tower.id, 1, tower.floors ORDER BY 1 ASC
7. SELECT tower.name AS flat__tower__name, SUM(bill.amount_paise) AS billed, COUNT(bill.id) AS bills FROM bill INNER JOIN flat ON (bill.flat_id = flat.id) INNER JOIN tower ON (flat.tower_id = tower.id) GROUP BY 1, flat.number ORDER BY flat.number ASC
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 totals.py
aggregate() ends a QuerySet: it sends one query and returns a dictionary, with a key made from the field and the
function, amount_paise__sum, unless you name it as line 2 did. annotate() keeps the QuerySet and gives each object
an annotation: A value that annotate() adds to every object of a QuerySet, worked out by the database in the same query, such as each flat's number of bills. It is read as an attribute of the object., here bill_count on every flat. Its name must not be one the model
already uses: bills is the related name of Bill.flat, so annotate(bills=…) raised a ValueError. The SQL of
line 3 grouped by the flat’s columns after a LEFT OUTER JOIN, so C-101, with no bill, came back with 0.
Put values() first, and annotate() groups by the fields values() names instead. Step through how line 4 turned
the 18 bills into two rows:
The drawing shows the whole scene; its 6 steps are listed below.
Every step as text
- Eighteen bills. Bill.objects starts from every bill: three months for each of the six flats of towers A and B. Tower C was handed over in October and has none.
- Each row gets its tower. values('flat__tower__name') names what to group by. The tower's name is two relations away, so the SQL joins flat and tower, and every bill row carries its tower's letter.
- Rows become groups. annotate() after values() groups by the fields values() named: the SQL says GROUP BY 1, the first column, which is the tower name. The 18 rows fall into two groups of 9.
- One sum per group. Sum('amount_paise') adds the amounts inside each group: 1,837,500 paise for tower A and 2,512,500 for tower B.
- Two dictionaries. One dictionary per group comes back. Tower C has no bill, so no joined row names it, and the result has no line for C; a query that starts from Tower keeps it.
- aggregate() instead. aggregate(Sum('amount_paise')) makes the whole QuerySet one group and returns a dictionary, not a QuerySet: {'amount_paise__sum': 4350000}, the 18 bills together.
An order_by() that splits the groups
Line 7 took line 4’s grouped query and ordered it by the flat number. Django adds the fields you order by to the
GROUP BY, so the result had six groups of 3 bills, one per flat, instead of two towers. Order such a query by
the field you grouped by, or clear the order with order_by() and no arguments.
Line 4 has no row for tower C, because no bill names it. Line 5 starts from Tower and keeps C, with None: SQL’s
SUM over no rows is NULL, and of the aggregates only Count gives 0. Sum(…, default=0) wraps the sum in
COALESCE(…, 0), which line 6 shows.
Key idea
aggregate() returns one dictionary for the whole QuerySet, annotate() gives each object a value, and
values() before annotate() decides what the groups are.
Checkpoint
Check: aggregate() and annotate()
Conditional counts and sums make the dues report exact
A dues report needs more than totals: of each tower’s bills, how many are unpaid, and how many flats are behind.
Both count some rows only. Count and Sum take a filter, a Q object, which became SQL’s FILTER (WHERE …):
"""The monthly dues report: for each tower, what was billed and paid, what is outstanding, how many bills are unpaid
and how many flats have an overdue bill, in one query.
Run it next to manage.py. seed.py makes db.sqlite3 there with every table and the lesson's rows.
"""
import seed # noqa: F401 (starts Django, creates the tables, adds the rows)
from django.db import connection
from django.db.models import Count, F, Q, Sum
from django.test.utils import CaptureQueriesContext
from seed import TODAY, compact
from society.models import Tower
unpaid = Q(flats__bills__paid_paise__lt=F('flats__bills__amount_paise'))
overdue = unpaid & Q(flats__bills__due_on__lt=TODAY)
with CaptureQueriesContext(connection) as queries:
report = (
Tower.objects.annotate(
billed=Sum('flats__bills__amount_paise', default=0),
paid=Sum('flats__bills__paid_paise', default=0),
unpaid_bills=Count('flats__bills', filter=unpaid),
overdue_flats=Count('flats', filter=overdue, distinct=True),
)
.annotate(outstanding=F('billed') - F('paid'))
.order_by('name')
)
print('tower billed paid outstanding unpaid bills overdue flats')
for tower in report:
print(f'{tower.name:>5} {tower.billed:>9,} {tower.paid:>9,} {tower.outstanding:>12,}'
f' {tower.unpaid_bills:>13} {tower.overdue_flats:>14}')
without_distinct = Tower.objects.annotate(overdue_flats=Count('flats', filter=overdue)).order_by('name')
print('overdue flats without distinct=True:', list(without_distinct.values_list('name', 'overdue_flats')))
print(len(queries), 'queries. The first one:')
print(compact(queries[0]['sql']))Output
tower billed paid outstanding unpaid bills overdue flats
A 1,837,500 1,325,000 512,500 3 1
B 2,512,500 1,162,500 1,350,000 5 2
C 0 0 0 0 0
overdue flats without distinct=True: [('A', 1), ('B', 3), ('C', 0)]
2 queries. The first one:
SELECT tower.id, tower.name, tower.floors, COALESCE(SUM(bill.amount_paise), 0) AS billed, COALESCE(SUM(bill.paid_paise), 0) AS paid, COUNT(bill.id) FILTER (WHERE bill.paid_paise < (bill.amount_paise)) AS unpaid_bills, COUNT(DISTINCT flat.id) FILTER (WHERE (bill.paid_paise < (bill.amount_paise) AND bill.due_on < '2026-10-20')) AS overdue_flats, (COALESCE(SUM(bill.amount_paise), 0) - COALESCE(SUM(bill.paid_paise), 0)) AS outstanding FROM tower LEFT OUTER JOIN flat ON (tower.id = flat.tower_id) LEFT OUTER JOIN bill ON (flat.id = bill.flat_id) GROUP BY tower.id, tower.name, tower.floors ORDER BY tower.name ASC
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 dues_report.py
unpaid_bills counted the bills whose paid_paise is below amount_paise, comparing two columns of the same row with
F(), which a later section looks at. overdue_flats counts flats, not bills, so it needs distinct=True: B-1205
owes for August and September, two overdue bills of one flat, and without distinct the last line gave tower B 3
overdue flats instead of 2. outstanding is an annotation built from two others, F('billed') - F('paid'), in the
same SELECT.
| Argument | What it adds to the SQL | What it did in the report |
|---|---|---|
filter=Q(…) |
COUNT(…) FILTER (WHERE …) |
counted the unpaid bills only, while the sums still saw every bill |
distinct=True |
COUNT(DISTINCT flat.id) |
counted B-1205 once, though two of its bills are overdue |
default=0 |
COALESCE(SUM(…), 0) |
gave tower C 0 paise instead of None |
Django’s documentation says that databases without FILTER get the same condition as a CASE inside the aggregate,
and that the two are equivalent, though FILTER may perform better. A filter() call on the QuerySet would be
something else: a WHERE that removes the paid bills from every column, the billed and paid totals too.
Checkpoint
Check: Conditional counts and sums
Two relations in one annotate() multiply the rows
The committee also wants each tower’s complaints next to its bills. Two counts over two relations look like one more
argument to annotate(), but they are not:
"""Two aggregates over two different relations in one annotate(): the joins multiply the rows, and both counts come
out wrong. distinct=True puts the counts right, but not a sum next to them.
Run it next to manage.py. seed.py makes db.sqlite3 there with every table and the lesson's rows.
"""
import seed # noqa: F401 (starts Django, creates the tables, adds the rows)
from django.db import connection
from django.db.models import Count, Sum
from django.test.utils import CaptureQueriesContext
from seed import compact
from society.models import Bill, Complaint, Tower
def show(label, towers, *names):
print(label)
for row in towers.order_by('name').values_list('name', *names):
print(' ', row[0], dict(zip(names, row[1:])))
print('Tower A has', Bill.objects.filter(flat__tower__name='A').count(), 'bills and',
Complaint.objects.filter(flat__tower__name='A').count(), 'complaints')
with CaptureQueriesContext(connection) as queries:
show('1. two counts in one annotate():',
Tower.objects.annotate(bills=Count('flats__bills'), complaints=Count('flats__complaints')),
'bills', 'complaints')
show('2. the same counts with distinct=True:',
Tower.objects.annotate(bills=Count('flats__bills', distinct=True),
complaints=Count('flats__complaints', distinct=True)), 'bills', 'complaints')
show('3. a sum next to a distinct count:',
Tower.objects.annotate(billed=Sum('flats__bills__amount_paise', default=0),
complaints=Count('flats__complaints', distinct=True)), 'billed', 'complaints')
print('The SQL of 1:')
print(compact(queries[0]['sql']))Output
Tower A has 9 bills and 7 complaints
1. two counts in one annotate():
A {'bills': 21, 'complaints': 21}
B {'bills': 21, 'complaints': 18}
C {'bills': 0, 'complaints': 1}
2. the same counts with distinct=True:
A {'bills': 9, 'complaints': 7}
B {'bills': 9, 'complaints': 6}
C {'bills': 0, 'complaints': 1}
3. a sum next to a distinct count:
A {'billed': 4537500, 'complaints': 7}
B {'billed': 6525000, 'complaints': 6}
C {'billed': 0, 'complaints': 1}
The SQL of 1:
SELECT tower.name AS name, COUNT(bill.id) AS bills, COUNT(complaint.id) AS complaints FROM tower LEFT OUTER JOIN flat ON (tower.id = flat.tower_id) LEFT OUTER JOIN bill ON (flat.id = bill.flat_id) LEFT OUTER JOIN complaint ON (flat.id = complaint.flat_id) GROUP BY tower.id, 1, tower.floors ORDER BY 1 ASC
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 two_joins.py
Tower A has 9 bills and 7 complaints, and line 1 said 21 and 21. Each relation adds a LEFT OUTER JOIN, and the two
joins pair every bill of a flat with every complaint of the same flat. Flat A-102 alone has 3 bills and 3 complaints,
which become these 9 rows:
| Joined row | Bill | Complaint |
|---|---|---|
| 1 | August | Seepage in the bathroom |
| 2 | August | Doorbell wiring |
| 3 | August | Corridor light off |
| 4 | September | Seepage in the bathroom |
| 5 | September | Doorbell wiring |
| 6 | September | Corridor light off |
| 7 | October | Seepage in the bathroom |
| 8 | October | Doorbell wiring |
| 9 | October | Corridor light off |
A-101 adds 3 × 1 rows and A-1101 3 × 3, so tower A’s group holds 3 + 9 + 9 = 21 rows, and COUNT counts rows.
Django’s documentation warns about exactly this: aggregations over several relations in one annotate() come out
wrong, because Django joins the relations where each would need a subquery of its own.
distinct=True puts the counts right, 9 and 7 in line 2, because it counts different ids, and a repeated row repeats
the same id. It cannot repair a sum. Line 3 billed tower A 4,537,500 paise instead of 1,837,500: every bill was added
once for each complaint of its flat, and DISTINCT on amounts would merge different bills that cost the same, such as
A-101’s three bills of 162,500. A sum next to another relation needs a subquery of its own, which the section on
Subquery builds.
Key idea
Every relation that one annotate() crosses multiplies the rows of the others: give each count distinct=True,
and give a sum or an average its own subquery.
Checkpoint
Check: Joins that multiply rows
F(), Case and When compute a value for every row in SQL
Some values belong to each row, not to a group: what a bill still owes, whether it is paid, part paid or unpaid,
whether it is overdue. F() expression: A reference to a column of the row the database is working on, written F('paid_paise'). Queries and updates use it to compare or compute with columns, as in F('amount_paise') - F('paid_paise'), without reading values into Python. and conditional expressions compute them in
the same SELECT, and update() can use them too:
"""Values worked out for every row inside the query: F() for columns, Case and When for choices, a Q object for a
yes or no, and F() in update() for a payment. Then the way integer division loses paise.
Run it next to manage.py. seed.py makes db.sqlite3 there with every table and the lesson's rows.
"""
import seed # noqa: F401 (starts Django, creates the tables, adds the rows)
from django.db import connection
from django.db.models import Case, F, Q, Value, When
from django.test.utils import CaptureQueriesContext
from seed import TODAY, compact
from society.models import Bill
september = (
Bill.objects.filter(month__month=9)
.annotate(
due=F('amount_paise') - F('paid_paise'),
state=Case(
When(paid_paise__gte=F('amount_paise'), then=Value('paid')),
When(paid_paise=0, then=Value('unpaid')),
default=Value('part paid'),
),
overdue=Q(due_on__lt=TODAY) & Q(paid_paise__lt=F('amount_paise')),
)
.order_by('flat__tower__name', 'flat__number')
)
with CaptureQueriesContext(connection) as queries:
print('flat amount paid due state overdue')
for tower, number, amount, paid, due, state, late in september.values_list(
'flat__tower__name', 'flat__number', 'amount_paise', 'paid_paise', 'due', 'state', 'overdue'):
print(f'{tower}-{number:<5} {amount:>8,} {paid:>9,} {due:>9,} {state:<9} {late}')
paid_now = Bill.objects.filter(flat__tower__name='B', flat__number=101, month__month=9).update(
paid_paise=F('paid_paise') + 100_000)
print('update() with F() changed', paid_now, 'row; B-101 now owes',
september.get(flat__tower__name='B', flat__number=101).due, 'for September')
print('The SQL of the report and of the update:')
for query in queries:
print(' ', compact(query['sql']))
thirds = Bill.objects.filter(month__month=10, flat__tower__name='A', flat__number=101).annotate(
third=F('amount_paise') / 3, lost=F('amount_paise') - 3 * (F('amount_paise') / 3),
third_exact=F('amount_paise') / 3.0).values('amount_paise', 'third', 'lost', 'third_exact')
print('Splitting A-101 October bill three ways:', thirds.get())Output
flat amount paid due state overdue
A-101 162,500 162,500 0 paid False
A-102 162,500 0 162,500 unpaid True
A-1101 287,500 287,500 0 paid False
B-101 200,000 100,000 100,000 part paid True
B-1203 287,500 287,500 0 paid False
B-1205 350,000 0 350,000 unpaid True
update() with F() changed 1 row; B-101 now owes 0 for September
The SQL of the report and of the update:
SELECT tower.name AS flat__tower__name, flat.number AS flat__number, bill.amount_paise AS amount_paise, bill.paid_paise AS paid_paise, (bill.amount_paise - bill.paid_paise) AS due, CASE WHEN bill.paid_paise >= (bill.amount_paise) THEN 'paid' WHEN bill.paid_paise = 0 THEN 'unpaid' ELSE 'part paid' END AS state, (bill.due_on < '2026-10-20' AND bill.paid_paise < (bill.amount_paise)) AS overdue FROM bill INNER JOIN flat ON (bill.flat_id = flat.id) INNER JOIN tower ON (flat.tower_id = tower.id) WHERE django_date_extract('month', bill.month) = 9 ORDER BY 1 ASC, 2 ASC
UPDATE bill SET paid_paise = (bill.paid_paise + 100000) WHERE bill.id IN (SELECT U0.id FROM bill U0 INNER JOIN flat U1 ON (U0.flat_id = U1.id) INNER JOIN tower U2 ON (U1.tower_id = U2.id) WHERE (U1.number = 101 AND U2.name = 'B' AND django_date_extract('month', U0.month) = 9))
SELECT bill.id, bill.flat_id, bill.month, bill.amount_paise, bill.paid_paise, bill.due_on, (bill.amount_paise - bill.paid_paise) AS due, CASE WHEN bill.paid_paise >= (bill.amount_paise) THEN 'paid' WHEN bill.paid_paise = 0 THEN 'unpaid' ELSE 'part paid' END AS state, (bill.due_on < '2026-10-20' AND bill.paid_paise < (bill.amount_paise)) AS overdue FROM bill INNER JOIN flat ON (bill.flat_id = flat.id) INNER JOIN tower ON (flat.tower_id = tower.id) WHERE (django_date_extract('month', bill.month) = 9 AND flat.number = 101 AND tower.name = 'B') LIMIT 21
Splitting A-101 October bill three ways: {'amount_paise': 162500, 'third': 54166, 'lost': 2, 'third_exact': 54166.666666666664}
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 per_bill.py
F('amount_paise') - F('paid_paise') became (bill.amount_paise - bill.paid_paise). The Case became CASE WHEN … THEN … ELSE … END, and a Q object given to annotate() became a true-or-false column, overdue. Step through how
the CASE decided the state of two September bills:
The drawing shows the whole scene; its 6 steps are listed below.
Every step as text
- One row at a time. The database works the CASE out for every row the query reads. B-101's September bill is for 200,000 paise, and 100,000 has been paid.
- The first When. When clauses are tried in order. Is 100,000 at least 200,000? No, so the first When does not decide this row.
- The second When. Is 100,000 equal to 0? No again, so the second When does not decide either.
- The default. No When was true, so the default decides: 'part paid'. Without a default the CASE would give NULL, which Python reads as None.
- The state column. The row leaves the database with state 'part paid', a column of the SELECT like any other. Python receives text, and no Python code decided anything.
- A row the first When decides. A-101 paid its September bill in full, so the first When is true and the CASE stops there: 'paid'. The later clauses are never tried for that row.
The clauses are tried in order and the first true one decides, as if, elif and else do in Python, so their order
is part of the rule. With no default, a row that no When matches gets NULL, which Python reads as None.
update(paid_paise=F('paid_paise') + 100_000) sent one UPDATE that adds the payment to whatever the row holds when
the statement runs, without reading the bill into Python first; B-101 then owed nothing for September. The lesson on
transactions in this module uses the same F() to stop two bookings overwriting each other’s count of free places.
Integer division drops paise
Splitting A-101’s October bill three ways with F('amount_paise') / 3 gave 54,166 paise: both sides are integers,
so SQLite divided as integers and dropped the fraction, and three such shares are 2 paise short of the 162,500
billed. Dividing by 3.0 gave a float, which is no better for money. Divide in whole paise and give the
remainder to one of the shares.
Checkpoint
Check: F(), Case and When
Subquery and Exists ask a second question about every row
The dues page also shows each flat’s latest bill and whether it has anything overdue: a question about other rows,
asked once for every flat. Subquery with OuterRef writes it as a query inside the query, a
correlated subquery: A query inside another query that refers to the outer query's current row, written with Subquery() or Exists() and OuterRef() in Django, so that it answers once for every row of the outer query., and Exists asks only whether such a row exists:
"""Subquery and OuterRef ask a second question about every row: each flat's latest bill. Exists asks whether a row of
another table matches: does the flat have an overdue bill? Last, a subquery keeps a sum away from another join.
Run it next to manage.py. seed.py makes db.sqlite3 there with every table and the lesson's rows.
"""
import seed # noqa: F401 (starts Django, creates the tables, adds the rows)
from django.db import connection
from django.db.models import Count, Exists, F, OuterRef, Subquery, Sum
from django.db.models.functions import Coalesce
from django.test.utils import CaptureQueriesContext
from seed import TODAY, compact
from society.models import Bill, Flat, Tower
latest = Bill.objects.filter(flat=OuterRef('pk')).order_by('-month')
overdue = Bill.objects.filter(flat=OuterRef('pk'), paid_paise__lt=F('amount_paise'), due_on__lt=TODAY)
with CaptureQueriesContext(connection) as queries:
flats = Flat.objects.annotate(
last_month=Subquery(latest.values('month')[:1]),
last_due=Subquery(latest.annotate(due=F('amount_paise') - F('paid_paise')).values('due')[:1]),
owes=Exists(overdue),
).order_by('tower__name', 'number')
print('flat latest bill still due has an overdue bill')
for tower, number, month, due, owes in flats.values_list('tower__name', 'number', 'last_month', 'last_due', 'owes'):
month = month.strftime('%B') if month else 'none' # Subquery gives None when a flat has no bill
due = f'{due:,}' if due is not None else 'None'
print(f'{tower}-{number:<5} {month:<11} {due:>9} {owes}')
late = Flat.objects.filter(Exists(overdue)).order_by('tower__name', 'number')
print('filter(Exists(...)):', [f'{t}-{n}' for t, n in late.values_list('tower__name', 'number')])
joined = Flat.objects.filter(bills__paid_paise__lt=F('bills__amount_paise'), bills__due_on__lt=TODAY)
print('the same through a join:', [f'{t}-{n}' for t, n in joined.order_by('tower__name', 'number')
.values_list('tower__name', 'number')])
billed = (Bill.objects.filter(flat__tower=OuterRef('pk')).values('flat__tower')
.annotate(total=Sum('amount_paise')).values('total'))
towers = Tower.objects.annotate(billed=Coalesce(Subquery(billed), 0), complaints=Count('flats__complaints'))
print('billed in a subquery, next to the complaints:', list(towers.order_by('name').values_list('name', 'billed',
'complaints')))
print('The SQL of the first query:')
print(compact(queries[0]['sql']))Output
flat latest bill still due has an overdue bill
A-101 October 0 False
A-102 October 162,500 True
A-1101 October 187,500 False
B-101 October 200,000 True
B-1203 October 0 False
B-1205 October 350,000 True
C-101 none None False
filter(Exists(...)): ['A-102', 'B-101', 'B-1205']
the same through a join: ['A-102', 'B-101', 'B-1205', 'B-1205']
billed in a subquery, next to the complaints: [('A', 1837500, 7), ('B', 2512500, 6), ('C', 0, 1)]
The SQL of the first query:
SELECT tower.name AS tower__name, flat.number AS number, (SELECT U0.month AS month FROM bill U0 WHERE U0.flat_id = (flat.id) ORDER BY 1 DESC LIMIT 1) AS last_month, (SELECT (U0.amount_paise - U0.paid_paise) AS due FROM bill U0 WHERE U0.flat_id = (flat.id) ORDER BY U0.month DESC LIMIT 1) AS last_due, EXISTS(SELECT 1 AS a FROM bill U0 WHERE (U0.due_on < '2026-10-20' AND U0.flat_id = (flat.id) AND U0.paid_paise < (U0.amount_paise)) LIMIT 1) AS owes FROM flat INNER JOIN tower ON (flat.tower_id = tower.id) ORDER BY 1 ASC, 2 ASC
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 subqueries.py
OuterRef('pk') stands for the id of the flat the outer query is on, so the subquery’s condition became
U0.flat_id = (flat.id). Used as a column, a subquery must return one column and at most one row, which
values('month') and [:1] see to. Step through the two subqueries for A-102 and for C-101, the flat without a bill:
The drawing shows the whole scene; its 6 steps are listed below.
Every step as text
- The outer query. The outer query reads the flats, A-101 first. For each of them the database works out the columns of the SELECT, and two of them are subqueries.
- OuterRef is A-102's id. On A-102's row, OuterRef('pk') stands for A-102's id, so the first subquery reads only A-102's bills, newest month first, and keeps one: LIMIT 1.
- One value comes back. The subquery returns one column of one row: October, the month of the latest bill. The second Subquery does the same for what that bill still owes, 162,500 paise.
- EXISTS for A-102. EXISTS asks whether any bill of A-102 is unpaid and past its due date. The September bill is, so the answer is true, and the database stops at that first row.
- C-101 has no bill. On C-101's row the subquery finds no bill at all. An empty subquery gives NULL, which Python reads as None, and EXISTS gives false.
- Each flat once. Every flat gets exactly one result row. The join version of the same question returned B-1205 twice, once for each of its two overdue bills; EXISTS cannot repeat a flat.
Exists became EXISTS(SELECT 1 … LIMIT 1), true or false, and filter(Exists(overdue)) kept each flat with an
overdue bill once. Filtering through the join instead, with bills__ lookups, returned B-1205 twice, once for each
overdue bill. A subquery that finds nothing gives NULL, as C-101’s None shows; Coalesce(Subquery(…), 0) turns it
into 0.
The last line is the repair that the section on joins needed. The tower’s total is a subquery that joins only the bills, grouped by tower, so the complaints join beside it cannot multiply it: tower A is back to 1,837,500 paise, next to its 7 complaints, in one query.
Key idea
A subquery asks a question about other rows once for every row of the outer query: OuterRef names the outer row,
a column subquery returns one value, and Exists returns true or false without repeating any row.
Checkpoint
Check: Subquery and Exists
Window functions rank the flats of each tower without merging them
Which flat of each tower has the most open complaints? GROUP BY tower would merge the flats into one row per tower.
A window function: An SQL function such as RANK() or ROW_NUMBER() that gives every row a value computed from the rows of its partition, without merging them as GROUP BY does. Django writes it as Window(Rank(), partition_by=…, order_by=…). keeps every row and gives it a value computed from the other rows
of its partition:
"""Window functions rank the flats of each tower by their open complaints, without merging the flats into one row
per tower. RANK, DENSE_RANK and ROW_NUMBER differ only on a tie, and tower A has one.
Run it next to manage.py. seed.py makes db.sqlite3 there with every table and the lesson's rows.
"""
import sqlite3
import seed # noqa: F401 (starts Django, creates the tables, adds the rows)
from django.db import connection
from django.db.models import Count, F, Q, Window
from django.db.models.functions import DenseRank, Rank, RowNumber
from django.test.utils import CaptureQueriesContext
from seed import compact
from society.models import Flat
by_tower = {'partition_by': F('tower'), 'order_by': F('open').desc()}
ranked = (
Flat.objects.annotate(open=Count('complaints', filter=Q(complaints__status='open')))
.annotate(
rank=Window(Rank(), **by_tower),
dense_rank=Window(DenseRank(), **by_tower),
row_number=Window(RowNumber(), partition_by=F('tower'), order_by=[F('open').desc(), F('number')]),
)
.order_by('tower__name', 'row_number')
)
with CaptureQueriesContext(connection) as queries:
print('SQLite', sqlite3.sqlite_version)
print('flat open rank dense_rank row_number')
for tower, number, open_, rank, dense, row in ranked.values_list(
'tower__name', 'number', 'open', 'rank', 'dense_rank', 'row_number'):
print(f'{tower}-{number:<5} {open_:>5} {rank:>5} {dense:>11} {row:>11}')
top = ranked.filter(rank=1)
print('filter(rank=1):', [f'{t}-{n}' for t, n in top.values_list('tower__name', 'number')])
print('The SQL of filter(rank=1):')
print(compact(queries[-1]['sql']))Output
SQLite 3.51.0 flat open rank dense_rank row_number A-102 3 1 1 1 A-1101 3 1 1 2 A-101 0 3 2 3 B-1205 4 1 1 1 B-1203 1 2 2 2 B-101 0 3 3 3 C-101 1 1 1 1 filter(rank=1): ['A-102', 'A-1101', 'B-1205', 'C-101'] The SQL of filter(rank=1): SELECT tower__name, number FROM ( SELECT * FROM ( SELECT tower.name AS tower__name, flat.number AS number, RANK() OVER (PARTITION BY flat.tower_id ORDER BY COUNT(complaint.id) FILTER (WHERE (complaint.status = 'open')) DESC) AS qual0, ROW_NUMBER() OVER (PARTITION BY flat.tower_id ORDER BY COUNT(complaint.id) FILTER (WHERE (complaint.status = 'open')) DESC, flat.number) AS qual1 FROM flat LEFT OUTER JOIN complaint ON (flat.id = complaint.flat_id) INNER JOIN tower ON (flat.tower_id = tower.id) GROUP BY flat.id, flat.tower_id, 2, flat.area_sqft, 1 ORDER BY 1 ASC, ROW_NUMBER() OVER (PARTITION BY flat.tower_id ORDER BY COUNT(complaint.id) FILTER (WHERE (complaint.status = 'open')) DESC, flat.number) ASC ) qualify WHERE qual0 = 1 ) qualify_mask ORDER BY 1 ASC, qual1 ASC
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 rank_flats.py
Window(Rank(), partition_by=F('tower'), order_by=F('open').desc()) became RANK() OVER (PARTITION BY flat.tower_id ORDER BY … DESC), ordered here by an aggregate, the count of open complaints. The three functions agree until two
rows tie, and tower A has a tie:
The drawing shows the whole scene; its 6 steps are listed below.
Every step as text
- One partition per tower. PARTITION BY tower ranks each tower on its own: tower A in the left column, tower B in the right. ORDER BY open DESC puts the most open complaints first.
- A tie shares rank 1. A-102 and A-1101 both have 3 open complaints. RANK gives both of them 1, and so does DENSE_RANK.
- RANK skips after a tie. Two flats are ahead of A-101, so RANK gives it 3: the rank of a row is 1 plus the number of rows before it with a higher count. DENSE_RANK gives 2, with no gap.
- ROW_NUMBER never ties. ROW_NUMBER numbers the rows 1, 2, 3 whatever the counts. On the tie it follows the second ORDER BY of the query, the flat number, so A-102 is 1 and A-1101 is 2.
- Tower B starts again. Tower B is a new partition, so its counting starts again at 1. With 4, 1 and 0 open complaints there is no tie, and the three functions agree: 1, 2, 3.
- filter(rank=1). Filtering on the rank keeps A-102, A-1101, B-1205 and C-101, the only flat of tower C. Django puts the ranked query inside an outer SELECT and filters there.
RANK gave A-102 and A-1101 rank 1 and A-101 rank 3; DENSE_RANK gave 1, 1 and 2; ROW_NUMBER never ties, so it
needs a second order for the tie, here the flat number, or the database decides which tied flat comes first.
Filtering on a window, filter(rank=1), needed an extra step. PostgreSQL’s documentation explains why: window
functions are worked out after WHERE, so a WHERE cannot test them. Django put the ranked query inside an outer
SELECT and filtered there; its documentation lists one limit, a filter that joins window conditions with OR or
XOR in a query that aggregates.
Checkpoint
Check: Window functions
One query instead of a loop: 408 queries became 1
The numbers of the opening come from this program. It adds a second estate of 20 towers with 20 flats each and 12
monthly bills a flat, then works out what every tower is owed twice: in Python over the rows, and in one
annotate():
"""The same report twice: what every tower is still owed, worked out by a Python loop over the rows and by one
annotate(). The program counts the queries and the rows each way reads, and times both (the best of 5 runs).
Run it next to manage.py. seed.py makes db.sqlite3 there with the lesson's rows; this program adds a second estate
of 20 towers with 20 flats each and 12 monthly bills a flat, so that there is something to measure.
"""
from datetime import date
from time import perf_counter
import seed # noqa: F401 (starts Django, creates the tables, adds the rows)
from django.db import connection
from django.db.models import F, Sum
from django.test.utils import CaptureQueriesContext
from society.models import Bill, Flat, Tower
towers = Tower.objects.bulk_create([Tower(name=f'G{i}', floors=20) for i in range(1, 21)])
flats = Flat.objects.bulk_create([Flat(tower=t, number=100 * floor + 1, area_sqft=1000)
for t in towers for floor in range(1, 21)])
months = [date(2025, 11, 1), date(2025, 12, 1)] + [date(2026, m, 1) for m in range(1, 11)]
Bill.objects.bulk_create([Bill(flat=f, month=m, amount_paise=250_000, due_on=m.replace(day=25),
paid_paise=0 if (f.pk + m.month) % 4 == 0 else 250_000)
for f in flats for m in months])
print(f'{Tower.objects.count()} towers, {Flat.objects.count()} flats, {Bill.objects.count():,} bills')
def in_python():
"""One query for the flats, then one for each flat's bills, and the sums in Python."""
owed, rows = {}, 0
for flat in Flat.objects.select_related('tower'):
rows += 1
for bill in flat.bills.all():
rows += 1
owed[flat.tower.name] = owed.get(flat.tower.name, 0) + bill.amount_paise - bill.paid_paise
owed.setdefault(flat.tower.name, 0)
return owed, rows
def in_the_database():
"""One query: the database sums each tower's bills and returns one row per tower."""
towers = Tower.objects.annotate(
owed=Sum(F('flats__bills__amount_paise') - F('flats__bills__paid_paise'), default=0))
owed = {tower.name: tower.owed for tower in towers}
return owed, len(owed)
results = {}
for way in (in_python, in_the_database):
best = None
for _ in range(5):
with CaptureQueriesContext(connection) as queries:
start = perf_counter()
owed, rows = way()
seconds = perf_counter() - start
best = seconds if best is None else min(best, seconds)
results[way.__name__] = (owed, best)
count = f'{len(queries)} {"query" if len(queries) == 1 else "queries"}'
print(f'{way.__name__:<16} {count:>11} {rows:>6,} rows read best of 5: {best * 1000:.1f} ms')
python_owed, python_time = results['in_python']
db_owed, db_time = results['in_the_database']
print('same totals:', python_owed == db_owed, '| owed in all:', f'{sum(db_owed.values()):,}', 'paise')
print('the database was at least 20 times faster in this run:', python_time > 20 * db_time)Output
23 towers, 407 flats, 4,818 bills in_python 408 queries 5,225 rows read best of 5: 163.5 ms in_the_database 1 query 23 rows read best of 5: 1.2 ms same totals: True | owed in all: 301,862,500 paise the database was at least 20 times faster in this run: True
This output changes from run to run: the times depend on the computer and on what else it is doing; the queries, the rows and the totals do not
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 loop_vs_query.py
in_python() sent 1 query for the flats, with their towers joined in by select_related(), and then 1 for the bills
of each of the 407 flats: 408 round trips, and a Python object for each of the 5,225 rows it read. in_the_database()
sent one query; the database read the same bills, added them up and sent back 23 rows, one per tower. The totals were
the same, 301,862,500 paise, and the last line checks that the query was more than 20 times faster: the round trips
and the objects are the cost.
Moving work into the database is right when the result is a summary of many rows. When each row needs Python, a function the database cannot run, keep the loop, but fetch the rows it needs in bulk; the next lesson of this module measures how.
SQL Formatter Paste one of the compact SQL lines from this page to read it one clause at a time.Checkpoint
Check: A loop moved into the database
Practise: the dues report and the ranks by hand
What the database did for the dues report and for RANK(), written in plain Python on rows that are dictionaries and
tuples. First the report, with its empty tower and its flats counted once; then the ranks within each tower, ties
included. Each step has tests you can run here and a hint:
Code lab · 2 steps
The dues report and the ranks by hand
Sum, count and rank the society's bills and complaints the way the annotated queries of this lesson do, in plain Python.
Exercise · Medium · Python
Write the dues report in plain Python
The dues report of this lesson is one query: towers LEFT OUTER JOIN their flats and bills, Sum(…, default=0) for the money and Count('flats', filter=…, distinct=True) for the flats with an overdue bill. Write the same report in plain Python, on bills that are dictionaries:
{'flat': 'B-1205', 'amount': 350000, 'paid': 0, 'due_on': date(2026, 9, 25)}
dues_summary(towers, bills, today) returns one dictionary per tower, in the order of towers: {'tower': 'B', 'billed': …, 'paid': …, 'outstanding': …, 'overdue_flats': …}, with the money in paise.
- A flat's tower is the part of its label before the hyphen.
- A tower without a single bill is still in the result, with zeros, as
default=0gives. - A bill is overdue when it is not fully paid and its due date is before
today. A flat with two overdue bills is one overdue flat.
Starter code · dues.py
def dues_summary(towers, bills, today):
"""One dict per tower of `towers`, in that order: billed, paid and outstanding paise, and the overdue flats."""
report = {}
for bill in bills:
tower = bill['flat'].split('-')[0]
row = report.setdefault(tower, {'tower': tower, 'billed': 0, 'paid': 0, 'outstanding': 0, 'overdue_flats': 0})
row['billed'] += bill['amount']
row['paid'] += bill['paid']
row['outstanding'] += bill['amount'] - bill['paid']
if bill['paid'] < bill['amount'] and bill['due_on'] < today:
row['overdue_flats'] += 1
return list(report.values())The sample tests · test_dues.py
from datetime import date
from dues import dues_summary
TODAY = date(2026, 10, 20)
AREAS = {'A-101': 650, 'A-102': 650, 'A-1101': 1150, 'B-101': 800, 'B-1203': 1150, 'B-1205': 1400}
PAID = { # the August, September and October bills of the lesson: 'all', 'none' or paise
'A-101': ['all', 'all', 'all'], 'A-102': ['all', 'none', 'none'], 'A-1101': ['all', 'all', 100_000],
'B-101': ['all', 100_000, 'none'], 'B-1203': ['all', 'all', 'all'], 'B-1205': ['none', 'none', 'none'],
}
BILLS = []
for flat, shares in PAID.items():
amount = AREAS[flat] * 250
for month, share in zip((8, 9, 10), shares):
paid = {'all': amount, 'none': 0}.get(share, share)
BILLS.append({'flat': flat, 'amount': amount, 'paid': paid, 'due_on': date(2026, month, 25)})
def row(tower, billed, paid, overdue):
return {'tower': tower, 'billed': billed, 'paid': paid, 'outstanding': billed - paid, 'overdue_flats': overdue}
def test_the_lesson_report():
"""the numbers of dues_report.py for towers A and B"""
assert dues_summary(['A', 'B'], BILLS, TODAY) == [row('A', 1837500, 1325000, 1), row('B', 2512500, 1162500, 2)]
def test_tower_without_bills():
"""tower C has no bill yet: it is in the report, with zeros"""
assert dues_summary(['A', 'B', 'C'], BILLS, TODAY)[2] == row('C', 0, 0, 0)
def test_order_of_towers():
"""the rows follow the order of towers, not the order of the bills"""
assert [r['tower'] for r in dues_summary(['C', 'B', 'A'], BILLS, TODAY)] == ['C', 'B', 'A']
def test_flat_counts_once():
"""B-1205 has two overdue bills but is one overdue flat"""
late = [b for b in BILLS if b['flat'] == 'B-1205']
assert dues_summary(['B'], late, TODAY) == [row('B', 1050000, 0, 1)]
def test_due_today_is_not_overdue():
"""a bill is overdue only when its due date is before today"""
bill = {'flat': 'A-102', 'amount': 162500, 'paid': 0, 'due_on': TODAY}
assert dues_summary(['A'], [bill], TODAY) == [row('A', 162500, 0, 0)]
def test_part_paid_is_unpaid():
"""a bill paid in part is still unpaid, and overdue once its date has passed"""
bill = {'flat': 'B-101', 'amount': 200000, 'paid': 100000, 'due_on': date(2026, 9, 25)}
assert dues_summary(['B'], [bill], TODAY) == [row('B', 200000, 100000, 1)]A hint
Start from the towers, not from the bills: make a row of zeros for every name in towers first, so a tower with no bill keeps its row. For the overdue flats keep a set of flat labels per tower, the way distinct=True counts each flat once, and count the set at the end.
Results of the sample tests
| Test | Result | Details |
|---|
What your code printed
The sample tests run on this device, in your browser (Pyodide): nothing is sent to mysmartcopilot.com. The first run downloads Python (about 13.5 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.
Exercise · Medium · Python
Rank the flats of each tower, ties included
rank_flats.py asked the database for Window(Rank(), partition_by=F('tower'), order_by=F('open').desc()). Do the same in plain Python.
rank_flats(rows) gets (tower, number, open_complaints) tuples in any order and returns (tower, number, open_complaints, rank) tuples, sorted by tower, then rank, then flat number. The rank works the way SQL's RANK() does:
- Each tower is ranked on its own: the flat with the most open complaints in a tower has rank 1.
- Flats with the same number of complaints in a tower share a rank.
- After a tie the next rank skips, as in 1, 1, 3: the rank of a flat is 1 plus the number of flats of its tower with more complaints.
Starter code · ranks.py
def rank_flats(rows):
"""(tower, number, open, rank) for each flat, ranked within its tower by open complaints, most first."""
ranked = []
for tower in sorted({tower for tower, _, _ in rows}):
flats = sorted((row for row in rows if row[0] == tower), key=lambda row: (-row[2], row[1]))
for position, (_, number, open_complaints) in enumerate(flats, start=1):
ranked.append((tower, number, open_complaints, position))
return rankedThe sample tests · test_ranks.py
from ranks import rank_flats
LESSON = [('A', 101, 0), ('A', 102, 3), ('A', 1101, 3), ('B', 101, 0), ('B', 1203, 1), ('B', 1205, 4), ('C', 101, 1)]
def test_the_lesson_ranks():
"""the RANK column of rank_flats.py"""
assert rank_flats(LESSON) == [
('A', 102, 3, 1), ('A', 1101, 3, 1), ('A', 101, 0, 3),
('B', 1205, 4, 1), ('B', 1203, 1, 2), ('B', 101, 0, 3),
('C', 101, 1, 1),
]
def test_tie_shares_a_rank_and_the_next_skips():
"""two flats with 3 complaints are both 1, and the next one is 3, not 2"""
ranks = [rank for _, _, _, rank in rank_flats([('A', 101, 0), ('A', 102, 3), ('A', 1101, 3)])]
assert ranks == [1, 1, 3]
def test_towers_are_ranked_apart():
"""C-101 is first in tower C, though tower A has flats with more complaints"""
assert ('C', 101, 1, 1) in rank_flats(LESSON)
def test_any_input_order():
"""the input order does not change the result"""
assert rank_flats(list(reversed(LESSON))) == rank_flats(LESSON)
def test_everyone_tied():
"""when every flat of a tower has the same count, all share rank 1"""
assert rank_flats([('B', 101, 2), ('B', 1203, 2), ('B', 1205, 2)]) == [
('B', 101, 2, 1), ('B', 1203, 2, 1), ('B', 1205, 2, 1)]A hint
Group the rows by tower in a dictionary first. Within one tower, a flat's rank needs no sorting at all: count the flats of the same tower that have more open complaints, and add 1. Sort the finished tuples at the end with a key of (tower, rank, number).
Results of the sample tests
| Test | Result | Details |
|---|
What your code printed
The sample tests run on this device, in your browser (Pyodide): nothing is sent to mysmartcopilot.com. The first run downloads Python (about 13.5 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.
Then the quiz on the seven ideas of this lesson, from the shape of aggregate() to the loop moved into the database;
it starts with the idea where your answers have been weakest:
Practice that adapts to you
Practise this lesson
14 questions about 7 ideas of this lesson. Each answer is explained at once, and what you get right is kept in this browser.
Interview questions
Warm-up (fresher): What is the difference between aggregate() and annotate()?
aggregate() computes over the whole QuerySet and returns a dictionary, such as
{'amount_paise__sum': 4350000}; it ends the QuerySet, so nothing can be chained after it. annotate() returns a
QuerySet in which each object carries the computed value, such as each flat’s number of bills, and it can be filtered,
ordered and annotated again. Called after values(), annotate() groups by the fields values() names, which gives
one row per group, for example per tower. Both run in the database, as one query.
Two design questions of this module start from this lesson’s queries: a dashboard whose counts come out too high, and a slow report to rewrite as queries.
Model answers open with any pass: ₹79 for 1 week ($2.99), one payment that never renews.
-
Mid to senior
A society dashboard shows each tower's bills, amount billed and complaints, from one annotate(). Since complaints were added to it, the bill counts and the amounts have both come out about three times too high. Explain the cause, and fix it so that the page still sends one query.
-
Mid to senior
The monthly dues report loops over 2,000 flats in Python, reads every bill and takes 9 seconds. Rewrite it so that the database does the work: what goes into annotate(), how towers without bills and part-paid bills are handled, and how you prove the new report gives the same numbers.
Key takeaways
aggregate()returns one dictionary for the whole QuerySet,annotate()gives every object a value, andvalues()beforeannotate()decides the groups, while a field you order by joins theGROUP BY.filter=counts or sums only matching rows,distinct=Truecounts each related object once, anddefault=0turns theNULLof an empty group into 0.- Two relations in one
annotate()multiply each other’s rows: counts needdistinct=True, and a sum needs a subquery of its own. F(),Case,WhenandQcompute a value for every row inside the SQL, andF()inupdate()changes a row from its current value; integer columns divide as integers.SubquerywithOuterRefasks one value about other rows for each row, andExistsasks yes or no without repeating rows.- Window functions such as
RANK()rank rows inside each partition and keep every row; one annotated query replaced a loop of 408 queries with the same totals.
Terms in this lesson
- aggregate function
- A function that turns many rows into one value, such as SUM, COUNT, AVG, MIN and MAX in SQL, or Sum, Count, Avg, Min and Max in Django. Over no rows every one of them but COUNT gives NULL.
- annotation
- A value that annotate() adds to every object of a QuerySet, worked out by the database in the same query, such as each flat's number of bills. It is read as an attribute of the object.
- correlated subquery
- A query inside another query that refers to the outer query's current row, written with Subquery() or Exists() and OuterRef() in Django, so that it answers once for every row of the outer query.
- F() expression
- A reference to a column of the row the database is working on, written F('paid_paise'). Queries and updates use it to compare or compute with columns, as in F('amount_paise') - F('paid_paise'), without reading values into Python.
- window function
- An SQL function such as RANK() or ROW_NUMBER() that gives every row a value computed from the rows of its partition, without merging them as GROUP BY does. Django writes it as Window(Rank(), partition_by=…, order_by=…).
References
- Django documentation: Aggregation (Django Software Foundation)
- Django documentation: Query expressions (Django Software Foundation)
- Django documentation: Conditional expressions (Django Software Foundation)
- Django documentation: Database functions (Django Software Foundation)
- Django documentation: QuerySet API reference (Django Software Foundation)
- Django 6.0 release notes (Django Software Foundation)
- Django 6.1 release notes (Django Software Foundation)
- PostgreSQL 18 documentation: Window Functions (tutorial) (The PostgreSQL Global Development Group)
Related tools
Report a problem with this lesson
Kept only in this browser. Your Learn progress