Stop a SQL join from multiplying your revenue

Revenue increases after joining orders to a customer table even though no new orders arrived. The query runs without errors, making this a data-grain problem rather than a syntax problem.

Why this happens

A join produces a row for each matching combination. Two customer records for one customer can duplicate every matching order. Joining order headers to line items can also repeat a header-level total.

Step-by-step solution

1. Write down the intended grain: one row per order, line item or customer. Count records and distinct order IDs before and after the join.

2. Check the right-hand join key for duplicates. Determine whether duplicates are invalid data or legitimate history that requires an effective-date condition.

3. Resolve the key relationship before aggregating. In pandas, use merge validation to make an expected many-to-one relationship explicit.

Worked example

import pandas as pd
orders = pd.DataFrame({'customer_id':[1,1], 'amount':[10,20]})
customers = pd.DataFrame({'customer_id':[1], 'name':['Ada']})
joined = orders.merge(customers, on='customer_id', validate='many_to_one')
assert len(joined) == 2
assert joined['amount'].sum() == 30

Verify the fix

The join must preserve both the order count and the relevant total. Add a duplicated customer record to the example: validation should reject it instead of silently returning inflated revenue.

Prevent a repeat

Do not fix this with SUM(DISTINCT amount): separate legitimate orders can have the same price. Enforce unique keys or document the deliberate many-to-many relationship.

Reference

Official documentation or standards guidance

An original Gistmax troubleshooting guide. Scenarios and sample values are illustrative; configuration depends on your software version and environment. Cover diagram: Gistmax Tutorials.