SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 93 of 250

Hard

Compute the median completed order value

Your task

Return a single median_cents value for completed orders. Use the arithmetic mean of the two middle amounts if there is an even number of orders.

The median is the middle value, or the average of the two middle values when the count is even.

Sample data

Table:shop_orders
idcustomer_idordered_onchannelstatustotal_cents
20112025-01-04onlinecompleted2500
20222025-01-10storecompleted1800
20322025-01-10storecompleted3200
20432025-01-18marketplacerefunded5000
20542025-02-02storecompleted2700
20612025-02-04onlinecompleted4000
20752025-02-18onlinecancelled2400
20862025-02-28onlinecompleted1700
20942025-03-16onlinecompleted2900
21082025-03-09storecompleted3100

Expected output

median_cents
2800
Show a hint

In a CTE, number sorted amounts and count all eligible rows. The middle positions are (n+1)/2 and (n+2)/2 using integer division.

Subqueries and CTEs guide

Use a query as a value

An aggregate without GROUP BY produces one row. A single-column subquery can therefore provide a scalar value for a comparison. Here each price is compared with an average calculated across the whole table.

SELECT title, price
FROM albums
WHERE price < (SELECT AVG(price) FROM albums);

Ask whether a related row exists

An EXISTS subquery only needs to produce one row to be true. It refers to a.id from the outer query, so the test is evaluated for the current album. The expression in the inner SELECT is immaterial to the existence test.

SELECT a.title
FROM albums AS a
WHERE EXISTS (
  SELECT 1
  FROM sales AS s
  WHERE s.album_id = a.id AND s.quantity > 1
);

Name intermediate results with WITH

A common table expression gives an intermediate query a name for a single statement. Here the CTE summarizes sales before joining them to albums. Read the CTE on its own first, then read the final SELECT to see how the summary is used.

WITH totals AS (
  SELECT album_id, SUM(quantity) AS copies
  FROM sales
  GROUP BY album_id
)
SELECT a.title, COALESCE(t.copies, 0) AS copies
FROM albums AS a
LEFT JOIN totals AS t ON t.album_id = a.id;
SQLiteCtrl / ⌘ + Enter

Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.

Results

Loading SQLite…