The overall average hides 4 different numbers

city splits city-air-quality into 4 groups. Here is what pm10 looks like inside each.

• Bellmoor — mean 49.12, median 47.00 (1,370 rows)
• Drayton — mean 35.80, median 35.20 (1,370 rows)
• Ashfield — mean 27.46, median 26.20 (1,370 rows)
• Corvallis Bay — mean 17.94, median 16.50 (1,370 rows)

Top to bottom that is 49.12 against 17.94, a spread of 173.8%. The pooled average is 33.09.

select city,
  count(*)                                          as rows,
  round(avg(pm10)::numeric, 2)                      as mean,
  percentile_cont(0.5) within group (order by pm10) as median
from city_air_quality
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 Bellmoor 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.