SQL Aggregations SQL Topic exerciseHardVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Regional Weighted Selling Price

Return one weighted selling-price row per reporting region, including regions with no item facts.

  • Joins
  • Aggregation
  • CASE expressions
  • Numeric functions
  • Type conversion

Exercise brief

Understand the request

Revenue analytics lead Commercial leadership needs an effective unit selling price that gives larger item quantities proportionate weight.

Calculate the realized unit price for every reporting region from one consistent valid-item population.

Return

  • Return region_id, region_code, valid_units, item_revenue, weighted_unit_price in this exact left-to-right order.

Constraints

  • Use item revenue divided by valid units, not AVG(unit_price) or an average of group averages.
  • A valid item has non-NULL quantity and unit_price; retain zero values and exclude incomplete rows from both numerator and denominator.
  • Return zero totals and a NULL weighted price when the valid-unit denominator is zero.
  • Round weighted_unit_price to 2 decimals and sort by 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

order_items

  • order_idINTEGER
  • quantityINTEGER
  • unit_priceDECIMAL(10,2)

Hints, when you need them

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

Hint 1

A weighted price is a ratio of additive totals. The numerator and denominator must use the same valid rows.

Hint 2

Start from regions, preserve empty members with LEFT JOIN, and use matching CASE predicates inside both SUM expressions.

Hint 3

SELECT r.region_id, SUM(CASE WHEN /* valid item */ THEN /* units */ ELSE 0 END) AS valid_units, SUM(CASE WHEN /* valid item */ THEN /* revenue */ ELSE 0 END) AS item_revenue, ROUND(/* revenue */ / NULLIF(/* units */, 0), 2) AS weighted_unit_price FROM /* preserved region-to-item 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, SUM(CASE WHEN i.quantity IS NOT NULL AND i.unit_price IS NOT NULL THEN i.quantity ELSE 0 END) AS valid_units, SUM(CASE WHEN i.quantity IS NOT NULL AND i.unit_price IS NOT NULL THEN i.quantity * i.unit_price ELSE 0 END) AS item_revenue, ROUND(CAST(SUM(CASE WHEN i.quantity IS NOT NULL AND i.unit_price IS NOT NULL THEN i.quantity * i.unit_price ELSE 0 END) * 1.0 / NULLIF(SUM(CASE WHEN i.quantity IS NOT NULL AND i.unit_price IS NOT NULL THEN i.quantity ELSE 0 END), 0) AS NUMERIC), 2) AS weighted_unit_price FROM regions r LEFT JOIN orders o ON o.region_id = r.region_id LEFT JOIN order_items i ON i.order_id = o.order_id GROUP BY r.region_id, r.region_code ORDER BY r.region_id;

Why this works

Correctness: weighted_unit_price is the ratio of total item revenue to total valid units, so a nine-unit line influences the metric nine times as much as a one-unit line. Edge case: CENTRAL has no facts and must return zero totals with a NULL ratio, while zero price remains a valid observation. Portability: SUM, CASE, NULLIF, and numeric casting are broadly portable, but engines can differ in decimal precision and division result types.

Success check

Each stable region key appears once and its weighted price reconciles to revenue divided by the same valid-unit population.

Expected result

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

region_idregion_codevalid_unitsitem_revenueweighted_unit_price
1NORTHEAST1538025.33
2NORTHWEST824030
3SOUTHEAST627045
4WEST321070
5CENTRAL00NULL
6UNASSIGNED28040

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.