fare_usd by borough: a 229% spread

Breaking fare_usd down by borough on ride-hail-trips, because the headline average is 22.16 and no segment is actually there.

• Airport — mean 55.92, median 43.53 (965 rows)
• Downtown — mean 17.92, median 13.14 (2,399 rows)
• Uptown — mean 17.51, median 13.15 (1,453 rows)
• Midtown — mean 17.39, median 12.97 (2,034 rows)
• Harbour — mean 17.01, median 12.80 (1,149 rows)

Top to bottom that is 55.92 against 17.01, a spread of 228.8%. The pooled average is 22.16.

select borough,
  count(*)                                              as rows,
  round(avg(fare_usd)::numeric, 2)                      as mean,
  percentile_cont(0.5) within group (order by fare_usd) 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 most in Airport, where the mean sits well above the median. 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.