SQL · hard · Asked 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.

How to answer it

Where the database has it, use the ordered-set aggregate:

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

PERCENTILE_CONT interpolates between the two middle values on an even count; PERCENTILE_DISC returns an actual value from the data. Say which one you mean.

MySQL has neither, so the portable answer is window functions: number the rows from both ends and keep the middle one or two.

SELECT AVG(amount) AS median_order_value
FROM (
  SELECT
    amount,
    ROW_NUMBER() OVER (ORDER BY amount) AS rn,
    COUNT(*) OVER () AS cnt
  FROM orders
) t
WHERE rn IN ((cnt + 1) / 2, (cnt + 2) / 2);

Integer division makes the two expressions equal on an odd count (one middle row) and adjacent on an even count (two rows, averaged). Median per group is the same query with PARTITION BY added to both windows.

• 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.

Related questions

• Write a query to find the second highest salary from an Employees table.
• Given orders(user_id, order_date, amount), compute 7-day rolling revenue per day.
• Explain the difference between INNER, LEFT, and FULL OUTER JOIN.
• Find the top 3 products by revenue within each category.
• Compute month-over-month user retention from an events table.
• Find users who logged in 3 or more consecutive days.

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