In Django's ORM, how do you annotate each sales region with its top customer by revenue, using Subquery and OuterRef or a Window function?

## The problem: greatest value per group "Revenue per region" is a `GROUP BY`. "The **top customer** in each region" is harder, because a plain aggregate gives you the maximum **value**, not the **row** that holds it. `Max("customers__orders__amount")` tells you the largest order, not who placed it. Django's ORM offers two tools for this: a **correlated subquery** built with `Subquery` and `OuterRef`, and a **window function** built with `Window`. ## Route 1: `Subquery` and `OuterRef` ```python from django.db.models import OuterRef, Subquery, Sum top = ( Customer.objects.filter(region=OuterRef("pk")) .annotate(revenue=Sum("orders__amount", default=0)) .order_by("-revenue") .values("name")[:1] ) regions = Region.objects.annotate(top_customer=Subquery(top)) ``` How the pieces work: - **`OuterRef("pk")`** acts like an `F()` expression that points at the **outer** query's row; it is resolved only when the inner QuerySet is embedded in the outer one. - The inner QuerySet is ordinary Django: filter, annotate, order. Its `Sum` is grouped per customer. - **`values("name")`** reduces it to **one column** and **`[:1]`** to **one row** (a `LIMIT 1`), the shape a scalar subquery in `SELECT` must have. - `get()` or `first()` cannot be used: they would try to run the inner query immediately, before `OuterRef` has anything to point at. - Return two facts (name and revenue) with two `Subquery` annotations, or annotate the customer's `pk` and fetch details separately. Watch the **`NULL` ordering**: a customer with no orders has a `NULL` sum, and PostgreSQL places `NULL`s **first** in descending order, so an inactive customer could "win". Either sum with `default=0`, as above, or order with `F("revenue").desc(nulls_last=True)`. ## Route 2: `Window` with `RowNumber` Start from customers instead and rank them inside each region: ```python from django.db.models import F, Sum, Window from django.db.models.functions import RowNumber ranked = ( Customer.objects.annotate( revenue=Sum("orders__amount", default=0), rank=Window( expression=RowNumber(), partition_by=[F("region")], order_by=F("revenue").desc(), ), ) .filter(rank=1) .select_related("region") ) ``` - **`Window(expression, partition_by=None, order_by=None, frame=None)`** produces `... OVER (PARTITION BY ... ORDER BY ...)`; the docs recommend wrapping column names in `F()` for `partition_by`. - **`RowNumber`** gives exactly one row per region even with ties; **`Rank`** or **`DenseRank`** return every tied customer. - **Filtering on a window annotation** (`.filter(rank=1)`) works since **Django 4.2**; Django wraps the query so the filter runs after the window is computed. Disjunctive (`OR`) filters that mix a window with other conditions on an aggregating query raise `NotImplementedError`. ## Reading the SQL of the `Subquery` route Printing `str(regions.query)` shows roughly this shape, which is worth being able to explain: 1. The outer `SELECT` lists the region columns plus one parenthesised subquery per `Subquery` annotation. 2. Inside, `WHERE U0.region_id = (region.id)` is the `OuterRef("pk")` correlation. 3. `GROUP BY U0.id` comes from the inner `annotate(revenue=Sum(...))`, grouping orders per customer. 4. `ORDER BY ... DESC LIMIT 1` comes from `order_by("-revenue")` and the `[:1]` slice. Two separate `Subquery` annotations (name and revenue) repeat that inner query; the database may or may not optimise the repetition, which is one reason to prefer the `Window` route when you need several columns of the winner. ## Choosing between them | | `Subquery` + `OuterRef` | `Window` + `RowNumber` | |---|---|---| | Starts from | `Region` rows | `Customer` rows | | Result | every region, `None` if no customers | only regions that have customers | | Extra columns of the winner | one `Subquery` per column | the whole `Customer` object | | Ties | whichever row sorts first | explicit: `RowNumber` vs `Rank` | | Cost | one inner query per outer row, in principle | one pass with a sort per partition | Neither is always faster; check the plan on real data. ## `Exists` for yes/no questions When you only need to know **whether** a related row exists, use **`Exists(queryset)`**, a `Subquery` subclass that emits `EXISTS (...)` and lets the database stop at the first match. It can annotate (`has_big_order=Exists(...)`), filter directly (`Region.objects.filter(Exists(big_orders))`) and be negated with `~Exists(...)` for `NOT EXISTS`. Django removes ordering inside it automatically, and it needs no `values()` because the columns are discarded.