Articles
Median and Percentile SQL Interview Questions: Even Counts, Ties, and Engine Differences
Practise median and percentile SQL interview questions with even counts, ties, NULLs, a tested window-function solution, and key database-engine differences.
A median SQL query can run successfully and still answer the wrong question. For the four values 2, 4, 8, 10, an interpolated median is 6, while a discrete 50th percentile is 4. Neither result is a syntax error. The difference is the definition you chose.
Strong median and percentile SQL interview answers start with the population, the estimator, and the required output grain. Then they handle even counts, repeated values, missing observations, and the target database. Practise those choices with Write SQL for cuisine median delivery times.
Evidence boundary: Engine behavior below comes from official documentation checked on September 19, 2026. Numerical fixtures and interview prompts are original teaching examples. Local verification checks the fixture and fallback logic; it is not a claim that every database engine was executed.
What does “find the median” actually require?
Ask what one observation represents. Is the interviewer asking for the median delivery time across completed orders, the median customer's average delivery time, or the median restaurant's daily delivery time? Those populations can produce different answers from the same underlying records.
Next clarify the convention. The familiar numerical median averages the two central values when the count is even. A discrete percentile instead returns an observed value. Ask whether null measurements should be excluded, whether canceled deliveries belong in the population, and whether every cuisine must appear in the output.
An empty population needs an explicit representation. Returning NULL communicates that there was no measured median; returning zero claims something different. If the requested output includes cuisines with no eligible orders, begin from a cuisine dimension and left join the computed results.
Original interview approach: say the contract aloud: “One row per cuisine, using non-null delivery minutes for completed orders, with the ordinary numerical median and null for a cuisine with no measurements.” This makes the later SQL testable.
Why do continuous and discrete percentiles disagree?
For a sorted non-null sample of size n, a common continuous percentile convention places fraction p at the one-based position 1 + (n - 1) × p, then interpolates between neighboring positions. At p = 0.5, an even sample lands halfway between the central pair.
The discrete definition selects the first sorted value whose cumulative proportion reaches p. For positive p, the corresponding position is ceil(n × p); at p = 0, choose the minimum rather than an impossible position zero. Repeated values retain their multiplicity.
These are the behaviors described for PostgreSQL's ordered-set percentile functions. Other statistical packages can offer additional quantile conventions, so “the 90th percentile” alone is not a complete reproducibility specification. PostgreSQL aggregate functions.
The following original fixtures expose the differences without a large dataset:
| Sorted observations | Continuous p50 | Discrete p50 |
|---|---|---|
| 2, 4, 8, 10 | 6 | 4 |
| 2, 4, 8 | 4 | 4 |
| 1, 1, 1, 9 | 1 | 1 |
| 7 | 7 | 7 |
| No non-null observations | NULL | NULL |
For the first row, continuous p90 is 9.4, while discrete p90 is 10. The first answer estimates a location between observations; the second supplies an observed threshold. State which behavior the business question needs.
How do you write the direct PostgreSQL solution?
Assume deliveries has one row per delivery, with cuisine, status, and numeric minutes. PostgreSQL supports this grouped ordered-set aggregate:
SELECT cuisine,
percentile_cont(0.5)
WITHIN GROUP (ORDER BY minutes) AS median_minutes,
percentile_disc(0.9)
WITHIN GROUP (ORDER BY minutes) AS observed_p90
FROM deliveries
WHERE status = 'completed'
AND minutes IS NOT NULL
GROUP BY cuisine;
The explicit null filter makes the population easy to inspect, even though these percentile aggregates already ignore null sorted inputs. This query returns only cuisines that have an eligible observation. A separate dimension join is needed to retain absent or all-null groups.
Do not confuse the ordering inside WITHIN GROUP with the order of displayed output rows. Add a final ORDER BY cuisine if the assessment requires deterministic presentation. An output checker can reject a correct set of values when the requested row order differs.
For financial or very large numeric values, inspect the return type and precision requirements. PostgreSQL's documented continuous form accepts double precision or interval input; a numeric-looking result is not automatically exact decimal arithmetic. Avoid promising exact cents merely because the source column used a decimal type.
How can you compute a median without percentile functions?
Use row positions rather than value ranks. Filter the eligible population, number each group's observations, count its rows, retain the middle position or pair, and average those retained values.
The following fallback uses FLOOR explicitly so it does not depend on whether division of integer operands truncates in the chosen engine. delivery_id is a unique, non-null tie-breaker. All cuisines listed in cuisines are preserved.
WITH ranked AS (
SELECT cuisine, minutes,
ROW_NUMBER() OVER (
PARTITION BY cuisine
ORDER BY minutes, delivery_id
) AS rn,
COUNT(*) OVER (PARTITION BY cuisine) AS n
FROM deliveries
WHERE status = 'completed'
AND minutes IS NOT NULL
), medians AS (
SELECT cuisine,
AVG(CAST(minutes AS DECIMAL(18, 4))) AS median_minutes
FROM ranked
WHERE rn IN (
FLOOR((n + 1) / 2.0),
FLOOR((n + 2) / 2.0)
)
GROUP BY cuisine
)
SELECT c.cuisine, m.median_minutes
FROM cuisines AS c
LEFT JOIN medians AS m ON m.cuisine = c.cuisine
ORDER BY c.cuisine;
For four observations, the selected positions are two and three. For five, both expressions identify position three, which is selected once by IN. Casting before AVG prevents an integer-returning average from discarding a fractional median in engines where that would occur. Choose a decimal precision appropriate to the actual range.
The unique tie-breaker stabilizes row identity. Equal measured values still count separately, and choosing one tied row rather than another cannot change the selected value. This is different from deduplicating measurements.
Why do ties break plausible-looking solutions?
RANK and DENSE_RANK describe peer groups, not every observation's sequential position. With 1, 1, 1, 9, dense ranks are 1, 1, 1, 2. Selecting a “middle rank” is not equivalent to selecting the middle observations.
Similarly, DISTINCT minutes changes the sample to 1, 9, whose ordinary median is 5. That result describes distinct measured values, not the original deliveries. Deduplicate only when multiple records represent the same real observation, using an appropriate business identifier and a stated version rule.
PostgreSQL documents that ROW_NUMBER counts rows, while ranking functions treat peers differently. It also documents the effect of window frames on aggregate window calls. PostgreSQL window functions.
A subtle fallback bug is writing COUNT(*) OVER (PARTITION BY cuisine ORDER BY minutes) and assuming it is the group size. The default frame can produce a running count that includes peers. Use the unordered partition count shown above when you need the whole population size for every row.
What changes in SQL Server, BigQuery, and MySQL?
Keep the estimator fixed while translating syntax. PostgreSQL's grouped aggregate shape should not be copied unchanged into an engine whose percentile function is analytic.
In SQL Server, the continuous form includes OVER:
SELECT DISTINCT cuisine,
PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY minutes)
OVER (PARTITION BY cuisine) AS median_minutes
FROM deliveries
WHERE status = 'completed'
AND minutes IS NOT NULL;
It repeats the partition result across input rows; this projection uses DISTINCT to reduce identical result rows. That DISTINCT is applied after percentile calculation, unlike deduplicating the input measurements. Microsoft's documentation specifies ignored nulls and a float(53) continuous result. SQL Server PERCENTILE_CONT.
In BigQuery GoogleSQL, the value is a function argument:
SELECT DISTINCT cuisine,
PERCENTILE_CONT(minutes, 0.5)
OVER (PARTITION BY cuisine) AS median_minutes
FROM deliveries
WHERE status = 'completed'
AND minutes IS NOT NULL;
GoogleSQL also documents RESPECT NULLS and IGNORE NULLS, with nulls ignored by default. If the specification deliberately includes nulls, remove the explicit filter and inspect those documented semantics. Do not assume another engine offers the same modifier. GoogleSQL navigation functions.
For MySQL 8.4, use the documented window primitives to build the positional fallback rather than assuming PostgreSQL's ordered-set syntax exists. Check the assessment's actual version: a solution requiring window functions is not portable to every historical MySQL environment. MySQL window functions.
Is the bottom quartile the same as the lowest 25% of rows?
Not necessarily. Suppose four restaurants have revenues 10, 10, 10, 100. A threshold of revenue at or below the discrete first quartile includes three restaurants, not one. Keeping tied revenues together can conflict with selecting exactly one quarter of the rows.
NTILE(4) divides rows into buckets and may split ties. A percentile threshold classifies values and may include more than 25% when many observations equal the boundary. Ask whether the goal is equal-sized groups, an inclusive value threshold, or a strict below-threshold filter.
Also distinguish the unit being ranked from the unit returned. To find orders from low-revenue restaurants, first compute restaurant revenue and the restaurant-level cutoff, then join qualifying restaurant identifiers back to orders. Computing a percentile directly over orders answers a different question and weights large restaurants more heavily.
What should you test before discussing performance?
Test an odd sample, an even sample with a fractional answer, repeated central values, one observation, an all-null group, and a group absent from the fact table. Include a canceled order with an extreme value to expose a missing population filter.
Then test join fan-out. If each delivery joins to several status-history records before the percentile is calculated, some observations receive extra weight. A correct percentile function cannot repair the wrong input grain. Compare row counts and unique delivery identifiers before and after each join.
For scale, inspect sorting, partition sizes, memory pressure, and the query plan. Approximate quantiles may be appropriate when the prompt accepts their error characteristics; do not silently replace an exact percentile. Ask whether an approximation guarantee concerns rank error or numerical distance between values.
Finally, do not average group medians to obtain the overall median. One group with observations 0, 0, 0 and another with 100 has group medians 0 and 100; their average is 50, while the pooled median is 0. Retain the required underlying distribution information.
Practise the contract before memorizing the syntax
The bucket-estimation exercise is a conceptual follow-up rather than a SQL syntax drill.
| Practice question |
|---|
| Write SQL for cuisine median delivery times |
| Find orders from bottom-quartile revenue restaurants |
| Analyze DoorDash Orders: High-Frequency Customers, Top Spenders, MoM Sales & Bottom-Percentile Reach |
| Write complex joins and window functions |
| Estimate percentile from buckets |
Start with the cuisine-median problem. Write the expected output for a four-row group before writing SQL, then add one duplicate, one null, and one excluded record. Explain which changes should affect the result. That gives your query a correctness argument before you adapt it to another engine.