SQL Aggregations SQL Topic exerciseHardVerified answerPostgreSQL live

Regional Order-Value Distribution

Return one continuous median and 90th-percentile order-value row per region with eligible values.

  • Joins
  • Aggregation
  • Numeric functions
  • Filtering
  • Sorting

Exercise brief

Understand the request

Marketplace strategy analyst Average order value hides skew, so planning needs a distribution summary that stays at order grain.

Measure median and upper-percentile order value from an explicit order-grain population.

Return

  • Return region_id, region_code, sample_order_count, median_order_value, p90_order_value in this exact left-to-right order.

Constraints

  • Use one order_amount per non-CANCELLED order; exclude NULL order_amount and retain zero.
  • Use PostgreSQL PERCENTILE_CONT ordered-set aggregates with fractions 0.5 and 0.9.
  • Report the non-NULL sample count and round both interpolated statistics to 2 decimals.
  • Sort by stable region_id.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

regions

  • region_idINTEGER
  • region_codeVARCHAR(30)

orders

  • order_idINTEGER
  • region_idINTEGER
  • statusVARCHAR(30)
  • order_amountDECIMAL(12,2)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Percentiles describe an ordered population; first confirm that each input row represents one eligible order.

Hint 2

PERCENTILE_CONT interpolates between adjacent values, so it can return a value not present in the source.

Hint 3

SELECT r.region_id, COUNT(/* eligible value */) AS sample_order_count, PERCENTILE_CONT(/* median fraction */) WITHIN GROUP (ORDER BY /* order-grain value */) AS median_order_value, PERCENTILE_CONT(/* upper fraction */) WITHIN GROUP (ORDER BY /* order-grain value */) AS p90_order_value FROM /* eligible order population */ GROUP BY /* stable region grain */;

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT r.region_id, r.region_code, COUNT(o.order_amount) AS sample_order_count, ROUND((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY o.order_amount))::numeric, 2) AS median_order_value, ROUND((PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY o.order_amount))::numeric, 2) AS p90_order_value FROM regions r INNER JOIN orders o ON o.region_id = r.region_id WHERE o.status <> 'CANCELLED' AND o.order_amount IS NOT NULL GROUP BY r.region_id, r.region_code ORDER BY r.region_id;

Why this works

Correctness: PERCENTILE_CONT orders one value per eligible order and linearly interpolates at the requested fractions. Edge case: WEST has an even-sized population, NORTHWEST has ties and a zero value, and one NULL order amount plus the cancelled order are excluded. Portability: PostgreSQL uses ordered-set aggregates; SQL Server and Oracle expose PERCENTILE_CONT as an analytic function, while SQLite and MySQL need guided rank/interpolation alternatives.

Success check

Each qualifying region appears once and the statistics match continuous interpolation over its ordered order-level values.

Expected result

Use this output to verify values, aliases, ordering, and row count.

region_idregion_codesample_order_countmedian_order_valuep90_order_value
1NORTHEAST3100172
2NORTHWEST3120120
3SOUTHEAST1240240
4WEST2105141
6UNASSIGNED18080

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.