The overall average hides 4 different numbers

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

• Bellmoor — mean 33.41, median 34.00 (1,370 rows)
• Drayton — mean 26.59, median 27.20 (1,370 rows)
• Ashfield — mean 21.32, median 21.70 (1,370 rows)
• Corvallis Bay — mean 14.59, median 14.90 (1,370 rows)

Top to bottom that is 33.41 against 14.59, a spread of 128.9%. The pooled average is 23.98.

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