The overall average hides 5 different numbers

borough splits ride-hail-trips into 5 groups. Here is what distance_km looks like inside each.

• Airport — mean 14.77, median 13.59 (965 rows)
• Uptown — mean 3.51, median 2.90 (1,453 rows)
• Downtown — mean 3.47, median 2.87 (2,399 rows)
• Midtown — mean 3.47, median 2.90 (2,034 rows)
• Harbour — mean 3.43, median 2.89 (1,149 rows)

Top to bottom that is 14.77 against 3.43, a spread of 331.0%. The pooled average is 4.83.

select borough,
  count(*)                                                 as rows,
  round(avg(distance_km)::numeric, 2)                      as mean,
  percentile_cont(0.5) within group (order by distance_km) as median
from ride_hail_trips
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 Airport 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.