The overall average hides 30 different numbers

country splits world-indicators into 30 groups. Here is what population looks like inside each.

• Zephyria — mean 210,452,288, median 206,766,199 (24 rows)
• Galenia — mean 201,687,648, median 198,929,727 (24 rows)
• Lumeria — mean 198,734,154, median 198,624,635 (24 rows)
• Delphine — mean 195,014,923, median 190,271,901 (24 rows)
• Marrowind — mean 186,363,342, median 186,715,647 (24 rows)
• Xanthe — mean 185,564,055, median 184,335,385 (24 rows)

Top to bottom that is 210,452,288 against 4,349,229, a spread of 4,738.8%. The pooled average is 130,641,222.

select country,
  count(*)                                                as rows,
  round(avg(population)::numeric, 2)                      as mean,
  percentile_cont(0.5) within group (order by population) as median
from world_indicators
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 Zephyria 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.