SQL, data modeling, data quality, EDA, visualization · insight-1039
Compute a median or percentile over the underlying rows, not as an average of group medians
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, Data scientist
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
A dashboard shows median fare per route and a stakeholder asks for the network median, so someone averages the eleven route medians.
Action
Recompute the percentile from row-level or weighted data at the requested level (PERCENTILE_CONT ... WITHIN GROUP, or an approximate sketch where the data are huge). If only pre-aggregated tables exist, say that the figure cannot be derived and offer the mean, which can be weighted correctly.
Reason
Percentiles depend on the entire distribution; only sums, counts, minima and maxima roll up exactly.
Boundary
Medians, percentiles, distinct counts and ratios shown at several hierarchy levels.
Symptom or risk
Medians are not additive: the average of group medians ignores group sizes and the shape inside each group, and it matches no real passenger's fare.
Exceptions
Groups of equal size and identical distributions, which is a rare accident.
Trade-offs
Row-level percentiles are slower and need the raw grain retained.
Pitfalls
Rolling percentiles up from hourly aggregates stored with their own rounding.
Example
Hypothetical: two routes with 10 and 990 passengers have median fares 80 and 200; the network median is near 200, but the average of medians is 140.
Lessons and concepts
Claims and their sources
Kimball describes non-additive facts as those that cannot be meaningfully summed and recommends storing additive components and computing derived values from aggregated totals.
PostgreSQL's ordered-set aggregates such as percentile_cont and mode ignore NULL inputs in the aggregated column.