← All briefings
Enterprise AIData analysisPilot evaluation

China AI Analytics: Count Orders Once After Joining Tables

Correct arithmetic can hide repeated orders. Test an AI analyst with equal-value orders, split shipments and unmatched rows before accepting its total.

China AI Tour Research5 min read
Conceptual retail image of boxed goods on a trolley beside partly empty shelves, with a worker checking a paper list.
Conceptual retail image of boxed goods on a trolley beside partly empty shelves, with a worker checking a paper list.

An AI analyst can add every number correctly and still overstate order value because it counts the same order more than once. During a China enterprise-AI evaluation, ask the assistant to combine two small tables, then inspect how many output rows each order creates before trusting the total.

Use synthetic tables in a read-only demonstration environment. Keep the business question precise: “What is the total value of all three orders, and how many shipment records belong to each?” This question includes orders with no shipment record.

Give each table a clear unit of observation

Prepare an order table with exactly one row per order. Use one currency, CNY, and exclude tax adjustments, cancellations and refunds from this fixture.

Order IDOrder value
A100
B100
C200

The expected total order value is CNY 400. A and B deliberately have the same value despite being different orders.

Prepare a second table containing three shipment records: S1 belongs to A, S2 belongs to A, and S3 belongs to B. C has no shipment record. Each shipment identifier is unique. The expected shipment counts are therefore A: 2, B: 1, C: 0.

Ask the assistant to describe what one row represents in each input. An order row and a shipment row describe different things. A split shipment can produce several shipment records for one order without creating another sale.

Inspect the intermediate rows

Have the host expose the intermediate table used for the calculation, whether the assistant generates a query, a spreadsheet transformation or another processing step. Keep order ID and shipment ID visible.

A left join that preserves every order and matches shipments by order ID produces these rows:

Order IDShipment IDRepeated order value
AS1100
AS2100
BS3100
CNone200

Summing the last column gives CNY 500. That is CNY 100 above the correct total because A appears twice. The relationship between the tables explains the error; arithmetic alone will not reveal it.

An inner join drops C because it has no matching shipment. Summing order value over those matched rows gives CNY 300. That smaller number also answers the wrong question: it repeats A while excluding C. Retain the unmatched-order check alongside the duplicate-row check.

Reject a shortcut that removes equal amounts

Ask how the assistant corrects the repeated values. Summing only distinct numeric amounts would add 100 and 200 once each, producing CNY 300. It would discard one legitimate CNY 100 order along with the repeated value.

The relevant identity is the order ID, not the amount. For this fixture, one sound approach is to count shipment records by order ID first, then attach those counts to the original one-row-per-order table. Treat a missing shipment count as zero while retaining C's order value.

The expected final output has three rows, shipment counts 2, 1 and 0, and order values 100, 100 and 200. Its shipment-count sum is three; its order-value sum is CNY 400. Require both results and the order IDs before accepting the explanation.

Change one relationship and repeat

Add shipment S4 to A without changing the orders. The order-value total must remain CNY 400, while A's shipment count becomes three and the total shipment count becomes four. This change tests whether adding a child record can inflate the parent-level measure.

Then remove all shipment records in a fresh copy of the fixture. Under the stated question, the three orders and CNY 400 remain, with zero shipment counts. A blank answer should prompt investigation of the join or filtering step.

The invoice row-reconciliation guide checks whether extraction preserves source lines. Here, the input rows are already correct; the test concerns how combining tables changes the counting unit.

NIST AI RMF 1.0 (2023), MEASURE 2.1 calls for documenting test sets, metrics and evaluation tools. Preserve these input tables, the generated transformation and its intermediate rows with the final answer. A reviewer should be able to establish which records contributed to each total before the result enters a management report.