# Analytics result reconciliation

Hypothetical USD teaching records. Arithmetic was checked locally; no SQL database, generated query, model, or production report was tested. Read [The SQL ran. Did it answer the right question?](https://caisi.dev/notes/ai-sql-result-semantics/).

## Completed metric contract

| Field                         | Declared teaching value                                               |
| ----------------------------- | --------------------------------------------------------------------- |
| Metric                        | Total approved order value; not recognized revenue or cash            |
| Population                    | Exactly order-101 and order-102                                       |
| Snapshot                      | Fixed hypothetical snapshot; no target date or live source            |
| What one order row represents | One row per order ID                                                  |
| Order totals                  | order-101 = 10,000 cents; order-102 = 10,000 cents                    |
| Currency                      | USD for every record; no conversion                                   |
| Included lines                | Complete declared lines; no tax, discounts, cancellations, or returns |
| Reference                     | The two supplied order records: 20,000 cents / USD 200                |
| Difference tolerance          | Zero cents for this integer-cent exercise                             |

## Joined rows

| Order / line       | Repeated order total / line amount, cents |
| ------------------ | ----------------------------------------- |
| order-101 / line-1 | 10,000 / 2,000                            |
| order-101 / line-2 | 10,000 / 3,000                            |
| order-101 / line-3 | 10,000 / 5,000                            |
| order-102 / line-4 | 10,000 / 4,000                            |
| order-102 / line-5 | 10,000 / 6,000                            |

## Reconciliation

| Candidate calculation                   | Result and disposition                                                                                               |
| --------------------------------------- | -------------------------------------------------------------------------------------------------------------------- |
| Sum repeated order totals after joining | 50,000 cents / USD 500; reject, +30,000 cents from reference                                                         |
| Sum distinct amount values              | 10,000 cents / USD 100; reject, −10,000 cents from reference                                                         |
| Sum one consistent total per order ID   | 20,000 cents / USD 200; matches the declared reference                                                               |
| Sum all supplied line amounts           | 20,000 cents / USD 200; valid only because this example has complete lines and defines each order total as their sum |

DISTINCT applied to the amount keeps unique amounts, not unique orders. These two orders have the same amount, so that calculation discards a real order. To count each order once, use the intended order IDs and a consistent total for each from the approved source version. Investigate conflicting totals instead of taking the first row. A matching grand total alone can hide missing and extra orders that offset each other.

## Blank record for a permitted analytical result

Start with these questions: what number was requested, which records belong in it, and what does one row represent before and after each join? Then reconcile the identities as well as the total.

| Question                                                                                   | Record before accepting the result |
| ------------------------------------------------------------------------------------------ | ---------------------------------- |
| What question must the number answer? Who is responsible for its exact definition?         | Record                             |
| Which records belong, which are excluded, and which period and timezone apply?             | Record                             |
| Which currency, units, exchange-rate rule if needed, and rounding apply?                   | Record                             |
| What does one row represent in each source and in the expected result?                     | Record                             |
| Which IDs match the records? How many matches are expected, and which are missing?         | Record                             |
| Which source and reference versions apply? Who can settle a definition conflict?           | Record                             |
| Which query or calculation version ran, and which records may it use?                      | Record                             |
| Does each order have one consistent total, and are all required lines present?             | Record                             |
| How do candidate and reference totals, counts, and IDs differ? List missing and extra IDs. | Record                             |
| What difference is permitted, and how is every mismatch explained?                         | Record                             |
| Do tests reject repeated order totals and distinct-amount totals? Retain each result.      | Not run                            |
| What conflicts or missing evidence remain, who owns them, and what is the next check?      | Record                             |
| Who will decide whether to accept, withhold, or correct the result, using which evidence?  | Unverified                         |

Retain unresolved differences. Do not change a reference answer merely to match the candidate. Keep private data and restricted source locations out of public copies.