SQL · hard · Reported at Airbnb

Calculate the median order value using SQL.

Asked in Data Analyst interviews, in the SQL round.

Short answer

Use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) where supported.

Sample data

These tables are loaded into an in-browser engine on this page, so you can run your own answer against them. No account needed.

orders

How to answer it

Definition

The middle value when the orders are sorted. Unlike an average there is no plain aggregate for it in standard SQL, because it depends on position rather than on a running total.

The direct way

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_order
FROM   orders;

Output

median_order
-------------
       48.50

The portable way

Where PERCENTILE_CONT is unavailable, number the rows from both ends and take the middle. This handles even counts by averaging the two middle values.

WITH ranked AS (
  SELECT amount,
         ROW_NUMBER() OVER (ORDER BY amount)      AS asc_rn,
         ROW_NUMBER() OVER (ORDER BY amount DESC) AS desc_rn
  FROM   orders
)
SELECT AVG(amount) AS median_order
FROM   ranked
WHERE  asc_rn IN (desc_rn, desc_rn - 1, desc_rn + 1);

CONT or DISC

PERCENTILE_CONT interpolates between the two middle values on an even count, so it can return a number nobody was actually charged. PERCENTILE_DISC returns an observed value. For money the interpolated version is the usual choice; for something like "the median plan a customer is on", the discrete one is right.

Key points

• The median resists outliers, which is why it beats the mean on revenue and latency.
• Even counts need a rule. Interpolate, or take a real row, and know which you used.
• PERCENTILE_CONT is an ordered-set aggregate, hence the WITHIN GROUP syntax.
• For a median per group, add GROUP BY, or PARTITION BY in the window version.

FAQ

• Why not AVG? A handful of enormous orders drags the mean somewhere no customer is. The median stays where the customers are.
• Is it slow? It needs a sort or an equivalent, so it costs more than AVG on a large table.
• Can I get several percentiles at once? Yes, one PERCENTILE_CONT expression per percentile in the same SELECT.

The 30-second answer

If the engine supports it, PERCENTILE_CONT of zero point five WITHIN GROUP ordered by amount. That is one line and it handles even counts by interpolating.

If not, I would number the rows ascending and descending with two ROW_NUMBERs and average the middle one or two, which is the portable version and gets the even case right. The thing I would raise is why we want a median at all: order values are skewed by a few large orders, so the mean sits above almost every customer while the median describes them. I would also ask whether we want the interpolated median or a real observed order, because those differ on an even count.

In the interview

• What they are checking: knowing the built-in, and being able to do it without one.
• Common mistake: AVG(amount), offered as an approximation. The median is asked for precisely because order values are skewed.

Practice this for real

• Check your resume against a Data Analyst job description, free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews, two free, no card