pm25 by city: a 173% spread

Breaking pm25 down by city on city-air-quality, because the headline average is 17.49 and no segment is actually there.

• Bellmoor — mean 25.93, median 25.20 (1,370 rows)
• Drayton — mean 18.95, median 18.40 (1,370 rows)
• Ashfield — mean 14.51, median 14.00 (1,370 rows)
• Corvallis Bay — mean 9.50, median 8.80 (1,370 rows)

Top to bottom that is 25.93 against 9.50, a spread of 172.9%. The pooled average is 17.49.

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