Aggregation prompts: pin the denominator and recompute the rate

Last updated: 4 Oct 202611 min read

tutorial

An aggregation prompt should state the unit of analysis, inclusion window, exclusion rules, deduplication key, numerator, denominator, and rounding policy. The model can propose those definitions and explain the result, but a deterministic query or program must count the records. Reconcile raw rows to eligible rows and eligible rows to each group before publishing percentages. A group rate and overall rate must use their own denominators; averaging group percentages without weights is generally wrong when group sizes differ. Treat a zero denominator and conflicting duplicate as explicit errors, not a zero-percent result.

Operational case

A six-shipment extract contains two late deliveries and one repeated event for the same shipment. Counting all seven rows would report two late rows out of seven, while deduplicating by shipment ID yields two of six, or 33.33 percent. The sample code rejects duplicates that disagree on region or lateness rather than silently keeping the last row. At full scale, the team reconciles 4,823 raw events to 4,723 unique shipments, excludes 23 unresolved statuses, and counts 282 late of 4,700 eligible shipments: 6.00 percent. North has 80 of 2,000 late; south has 202 of 2,700. Those group rates differ, so a narrative should name the cohort before suggesting any reason for the difference.

shipments = [
    {"shipment_id": "SH-841", "region": "north", "late": False},
    {"shipment_id": "SH-842", "region": "south", "late": True},
    {"shipment_id": "SH-843", "region": "south", "late": False},
    {"shipment_id": "SH-844", "region": "north", "late": True},
    {"shipment_id": "SH-845", "region": "south", "late": False},
    {"shipment_id": "SH-846", "region": "north", "late": False},
    {"shipment_id": "SH-842", "region": "south", "late": True},
]

unique_shipments = {}
for shipment in shipments:
    shipment_id = shipment["shipment_id"]
    previous = unique_shipments.get(shipment_id)
    if previous is not None and previous != shipment:
        raise ValueError(f"conflicting duplicate: {shipment_id}")
    unique_shipments[shipment_id] = shipment

late_count = sum(shipment["late"] for shipment in unique_shipments.values())
denominator = len(unique_shipments)
if denominator == 0:
    raise ValueError("empty shipment cohort")
late_rate = 100 * late_count / denominator
print(f"unique={denominator} late={late_count} rate={late_rate:.2f}%")

Performance and operating cost

For R input rows, hash-based deduplication and counting take O(R) expected time and O(U) space for U unique shipment IDs. Grouping by G regions adds O(G) counters. A database query may move the work closer to storage, but its result still needs a reconciled filter and key definition. The model's token use should depend on a small schema and aggregate output, not the entire row set. Recompute percentages from checked counts and display enough precision to avoid hiding a material change behind rounding.

Common Mistakes

Do not use raw event rows as the denominator when the question asks about shipments.
Do not average regional rates without weighting by regional shipment counts.
Do not resolve conflicting duplicate records by whichever row appears last.