The overall average hides 6 different numbers

category splits retail-orders into 6 groups. Here is what unit_price_usd looks like inside each.

• electronics — mean 221.89, median 209.87 (960 rows)
• home — mean 81.72, median 76.30 (1,279 rows)
• sports — mean 66.73, median 63.05 (789 rows)
• apparel — mean 48.81, median 46.19 (1,696 rows)
• beauty — mean 29.37, median 27.88 (1,088 rows)
• grocery — mean 19.95, median 18.78 (1,188 rows)

Top to bottom that is 221.89 against 19.95, a spread of 1,012.1%. The pooled average is 72.66.

select category,
  count(*)                                                    as rows,
  round(avg(unit_price_usd)::numeric, 2)                      as mean,
  percentile_cont(0.5) within group (order by unit_price_usd) as median
from retail_orders
group by 1
order by mean desc;

A spread that wide means the pooled number is not a summary, it is an artefact of the mix. Change the proportion of electronics rows and the overall average moves without any individual group changing at all — which is how a metric goes up while every segment goes down.

Notice the mean and median columns disagree only slightly here. Always compute both in the group-by. The comparison between them per segment is free and tells you whether you are looking at a level difference or a tail difference.

This is the setup for Simpson's paradox — the case where every segment moves one way and the total moves the other.