SQL percentiles for skewed delivery times

A percentile summarizes a position in a distribution, but its exact value depends on the calculation convention. On a small dataset, two legitimate percentile definitions can give visibly different answers. State the method, sample size and eligible population when reporting p95 delivery time.

The synthetic advanced SQL lab contains eight completed delivery durations in minutes: 10, 20, 20, 25, 30, 40, 100 and 240. The long final observations make the distribution skewed. These values demonstrate calculations; they are not evidence about a real courier or a service benchmark.

Establish the central values

WITH ranked AS (
    SELECT minutes,
           ROW_NUMBER() OVER (ORDER BY minutes, delivery_id) AS position,
           COUNT(*) OVER () AS sample_size
    FROM deliveries
    WHERE minutes IS NOT NULL
)
SELECT MAX(sample_size) AS n,
       AVG(1.0 * minutes) AS mean_minutes,
       AVG(CASE WHEN position IN ((sample_size + 1) / 2,
                                  (sample_size + 2) / 2)
                THEN 1.0 * minutes END) AS median_minutes
FROM ranked;

The sample size is eight, mean 60.625 minutes and median 27.5 minutes. Integer division in this SQLite expression selects positions four and five for an even sample, or the same central position twice for an odd sample. The AVG operates over selected observations, so the repeated position in an odd sample is not double-counted.

The mean reflects the size of long delays. The median describes the middle of the ordered observations. Neither replaces the other, and neither alone shows the tail.

Calculate a nearest-rank p95

Define nearest-rank p95 as the observation at position ceiling(0.95 × n), using positions starting at one. For eight observations that is ceiling(7.6), or position eight: 240 minutes.

WITH ranked AS (
    SELECT minutes,
           ROW_NUMBER() OVER (ORDER BY minutes, delivery_id) AS position,
           COUNT(*) OVER () AS n
    FROM deliveries WHERE minutes IS NOT NULL
)
SELECT minutes AS nearest_rank_p95
FROM ranked
WHERE position = (95 * n + 99) / 100;

For a positive integer n, the integer arithmetic implements ceiling(95n/100). The output is an observed duration. On this tiny sample, p95 is simply the maximum; the label does not make it a stable estimate of a larger population's tail.

Compare an interpolated p95

Another convention sets the zero-based location to 0.95 × (n − 1). Here that is 6.65, between zero-based positions six and seven: 100 and 240 minutes. Linear interpolation gives 100 + 0.65 × 140 = 191 minutes.

WITH ranked AS (
    SELECT minutes,
           ROW_NUMBER() OVER (ORDER BY minutes, delivery_id) - 1 AS idx,
           COUNT(*) OVER () AS n
    FROM deliveries WHERE minutes IS NOT NULL
), positions AS (
    SELECT *, 0.95 * (n - 1) AS target,
           CAST(0.95 * (n - 1) AS INTEGER) AS lower_idx
    FROM ranked
)
SELECT MAX(CASE WHEN idx = lower_idx THEN minutes END)
       + (MAX(target) - MAX(lower_idx)) *
         (MAX(CASE WHEN idx = MIN(lower_idx + 1, n - 1) THEN minutes END)
          - MAX(CASE WHEN idx = lower_idx THEN minutes END))
       AS interpolated_p95
FROM positions;

Expect approximately 191 minutes, allowing ordinary floating-point representation. This is a distribution summary under the stated convention, not an actual observed delivery duration. The SQL uses standard window constructs supported by the SQLite window-function implementation, avoiding reliance on an optional percentile extension.

Check who is missing from the distribution

If only completed deliveries enter the table, an extremely delayed delivery still in transit is excluded. That can make today's completed-delivery p95 look better while customers continue waiting. Report open-delivery age and completion coverage alongside completed-duration statistics when assessing operations.

Define the clock start and stop: order placement to doorstep differs from dispatch to doorstep. Separate cancelled deliveries, invalid negative durations and timezone parsing failures. Do not silently drop them and describe the remainder as all deliveries.

Never average regional p95 values to obtain overall p95. Recalculate from the pooled eligible observations, or use an appropriate mergeable distribution representation with disclosed approximation. Regional sample sizes and distribution shapes matter.

Exercise: remove the 240-minute observation, recompute both p95 definitions and explain the change. Then mark that delivery unfinished instead of deleting it, and propose a second metric that keeps the unresolved wait visible.

NeuraPath's Data Analytics with Generative AI course links statistical interpretation with SQL reporting. A useful percentile project includes the method, eligibility checks and tail investigation rather than only a dashboard number.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Deduplicate change events using a deterministic tie-breaker.
• Continue with Compare month-to-date sales fairly across unequal months.