revenue_usd by category: a 1,000% spread

Breaking revenue_usd down by category on retail-orders, because the headline average is 111 and no segment is actually there.

• electronics — mean 340, median 245 (960 rows)
• home — mean 119, median 84 (1,279 rows)
• sports — mean 103, median 77 (789 rows)
• apparel — mean 75, median 52 (1,696 rows)
• beauty — mean 47, median 32 (1,088 rows)
• grocery — mean 31, median 23 (1,188 rows)

Top to bottom that is 340 against 31, a spread of 1,000.1%. The pooled average is 111.

select category,
  count(*)                                                 as rows,
  round(avg(revenue_usd)::numeric, 2)                      as mean,
  percentile_cont(0.5) within group (order by revenue_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 most in electronics, where the mean sits well above the median. 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.