Median Order Value per Country

Difficulty: Medium · Topics: median, percentiles, window-functions · Asked at: Google, Airbnb, Lyft

Problem

Return the median order amount per country (average of the two middle values when the count is even), ordered by country.

Schema and sample data

CREATE TABLE orders (order_id INTEGER, country TEXT, amount INTEGER);
INSERT INTO orders VALUES
(1,'DE',10),(2,'DE',30),(3,'DE',20),(4,'DE',100),
(5,'FR',5),(6,'FR',50),(7,'FR',15),
(8,'US',7);

Expected output

Hints

Solution

WITH r AS (
  SELECT country, amount,
         ROW_NUMBER() OVER (PARTITION BY country ORDER BY amount) AS rn,
         COUNT(*)     OVER (PARTITION BY country)                  AS n
  FROM orders
)
SELECT country, AVG(amount) AS median_amount
FROM r
WHERE rn IN ((n + 1) / 2, (n + 2) / 2)
GROUP BY country
ORDER BY country;

Explanation

n=4 (DE): rows 2 and 3 (20, 30) → 25. n=3 (FR): (3+1)/2 = 2 and (3+2)/2 = 2 → row 2 only → 15. n=1: row 1.

In practice: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) (Postgres/Snowflake), MEDIAN() (Snowflake, Databricks), percentile_approx(amount, 0.5) (Spark, approximate, distributed-friendly). Exact medians need a global sort per group, which is expensive at scale. Know when approximate is acceptable.

Follow-up questions