Write a query that returns customers whose total spend is above the 90th percentile of all customers.

Answer

Compute the 90th percentile of total spend across all customers, then filter to customers above that threshold.

Solution

WITH customer_spend AS (
  SELECT customer_id, SUM(order_value) AS total_spend
  FROM Orders
  GROUP BY customer_id
),
threshold AS (
  SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY total_spend) AS p90
  FROM customer_spend
)
SELECT cs.customer_id, cs.total_spend
FROM customer_spend cs, threshold t
WHERE cs.total_spend > t.p90;

Step by Step

1. The customer_spend CTE first collapses each customer's orders into one total-spend figure.
2. PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY total_spend) computes the value below which 90% of customers' spend falls - the 90th percentile threshold, using linear interpolation between the two nearest data points.
3. The final query keeps only customers whose spend exceeds that single threshold value.

Alternative Method: NTILE() for Engines Without PERCENTILE_CONT

WITH customer_spend AS (
  SELECT customer_id, SUM(order_value) AS total_spend
  FROM Orders GROUP BY customer_id
),
bucketed AS (
  SELECT customer_id, total_spend, NTILE(10) OVER (ORDER BY total_spend) AS decile
  FROM customer_spend
)
SELECT customer_id, total_spend FROM bucketed WHERE decile = 10;

NTILE(10) splits customers into 10 equal-sized buckets by spend; the top bucket (decile 10) approximates the top 10%. This is an approximation, not a true percentile calculation - PERCENTILE_CONT is the more mathematically precise answer where supported.

Key Exam Traps

• PERCENTILE_CONT vs PERCENTILE_DISC: _CONT interpolates between values (a "continuous" estimate); _DISC picks an actual existing data point. Confusing the two gives subtly different threshold values - know which one the interviewer means by "90th percentile."
• NTILE is not a true percentile: With an uneven number of customers, NTILE(10) buckets can have slightly different sizes, and its "top bucket" isn't mathematically identical to "above the 90th percentile" - a common point interviewers probe on.
• Aggregating before computing percentile is essential - computing the percentile over raw Orders rows instead of pre-aggregated total_spend per customer would answer a completely different question.