Quick answer: If order IDs are unique only within a source system, join on both source_system and order_id. Joining on order_id alone can match another system’s customer and duplicate revenue. Confirm the identity rule and lookup uniqueness first.

By Prakhar Shrivastava · October 21, 2026

What if order IDs repeat across source systems?

In this fictional exercise, three regional systems each assign their own order IDs. US order 101 is worth $100, UK order 101 is worth $80, and German order 202 is worth $60. Amounts are already normalized to USD. The source code is a namespace, not a currency or a customer location.

The lookup has exactly one customer assignment per source-system and order-ID pair. Its name, customers, is illustrative: this is an order-to-customer assignment table, not a customer master. The intended output is one row per source order, totaling $240.

Run the incomplete join

This complete CTE fixture uses common PostgreSQL-compatible SQL and was executed in SQLite for the checks.

WITH orders(source_system, order_id, amount_usd) AS (
  VALUES ('US',101,100), ('UK',101,80), ('DE',202,60)
), customers(source_system, order_id, customer_name) AS (
  VALUES ('US',101,'US customer'), ('UK',101,'UK customer'),
         ('DE',202,'DE customer')
)
SELECT o.source_system, o.order_id, o.amount_usd,
       c.customer_name
FROM orders o
LEFT JOIN customers c ON c.order_id = o.order_id
ORDER BY o.source_system, c.customer_name;

Expected: five rows and $420 if you sum the output amounts. Both copies of order 101 match both regional assignments. Germany contributes one row. The US amount repeats twice ($200), the UK amount repeats twice ($160), and Germany contributes $60.

Each source table can be perfectly valid while the join is wrong. The error is assuming order_id identifies an order globally. Adding DISTINCT to the selected columns does not remove the two different customer names attached to each order.

Join on the complete identity

Keep the same CTEs and replace the final SELECT with this query:

SELECT o.source_system, o.order_id, o.amount_usd,
       c.customer_name
FROM orders o
LEFT JOIN customers c ON c.source_system = o.source_system
 AND c.order_id = o.order_id
ORDER BY o.source_system, c.customer_name;

source_system  order_id  amount_usd  customer_name
DE                  202          60  DE customer
UK                  101          80  UK customer
US                  101         100  US customer

The corrected output has three rows totaling $240, with each amount attached to its own source’s customer. The extra AND is justified by the documented identity rule, not by whichever combination happens to produce the desired total.

Check the lookup before trusting the fix

Using the same fixture, this query should return no rows:

SELECT source_system, order_id, COUNT(*) AS assignments
FROM customers
GROUP BY source_system, order_id
HAVING COUNT(*) > 1;

If it returns duplicates, the composite join can still multiply rows. Resolve whether the assignment table contains history, replayed records, or conflicting assignments. A historical table may require an effective-time rule as well as the identity key. Arbitrary MAX or DISTINCT does not establish which assignment was valid.

Check missing source codes and order IDs separately. Ordinary equality does not match nulls. This LEFT JOIN retains such orders with a missing customer assignment; an inner join would remove them. Do not label every unmatched row as a new customer.

How should you validate the result?

• Establish the source grain and key contract before writing the join.
• Check lookup uniqueness on the full key, not order_id alone.
• Compare row counts and USD totals for the same intended population.
• Inspect key-to-customer pairs. Equal totals can hide incorrect attribution.
• Report unmatched orders separately and investigate incomplete loads or inconsistent source codes.

Keep source_system and order_id as separate join columns. Concatenation requires careful encoding and can create collisions; a numeric-looking ID should not be converted to a number if leading zeros are meaningful.

For an interview explanation: “Order IDs are unique only within each source. I will join on source_system and order_id, validate the lookup at that grain, and reconcile both amounts and customer assignments.”

References and related practice

The PostgreSQL joined-table documentation explains matching rows through an ON condition. This exercise’s namespace, values and expected outputs are original illustrative assumptions.

Continue with why SUM(DISTINCT amount) does not repair joins, counting distinct column pairs, and orders and payments reconciliation.