The total came out 4.2× too high

A fan-out, arithmetic first. retail-orders has 7,000 rows and 1,649 distinct customer_id values.

That is 4.2 rows per customer on average. Join anything to this table on customer_id and every row on the other side is duplicated that many times. If the other side carries a number you then sum, the sum is wrong by roughly that factor.

The reason it is hard to catch is that nothing fails. The query runs, the join is valid, and the result is a plausible-looking number that is 4.2 times too large.

-- the check, before the join
select count(*) as rows, count(distinct customer_id) as keys
from retail_orders;
-- rows 7,000, keys 1,649 → not unique

-- so aggregate to the grain you want first
with per_key as (
  select customer_id, sum(unit_price_usd) as unit_price_usd
  from retail_orders
  group by 1
)
select * from per_key;

Aggregating before the join is the fix that generalises. The alternatives — joining and then deduplicating, or select distinct over the result — both work occasionally and hide the problem the rest of the time, because they remove duplicate rows rather than duplicate contribution.

The two-line cardinality check above is cheaper than the incident. It belongs in front of every join you did not write yourself. Longer version in Joins that do not fan out and the fan-out pattern.