The Median Price

An interviewer asks: what is the median product price? The median is the middle value once the values are sorted, with as many below it as above it. With an even count there are two middle values, and the median is their average. SQLite has no MEDIAN function, so you build one from parts you already know.

The idea in one sentence: number the sorted prices, count them, and average the middle row or the middle two. Two window functions give every row what it needs: ROW_NUMBER() OVER (ORDER BY …) is its place in the sorted list, and COUNT(*) OVER () is how many rows there are, repeated on every row (a plain COUNT(*) would fold them into one row).

Finding the middle rows: in SQLite, a whole number divided by a whole number gives a whole number.

SELECT 7 / 2, 8 / 2, 7 / 2.0
-- 3 | 4 | 3.5

So for six sorted values 2, 6, 10, 14, 22, 110, the rows (6 + 1) / 2 and (6 + 2) / 2 are 3 and 4, and the median is (10 + 14) / 2 = 12. For seven values both give row 4: the one middle value.

What the check prints: it runs your query on the shop's 8 products, then adds a Kettle at 35 and runs your query again.

median
13.5
(1 row)
median
15
(1 row)

Your task: in query, write a named query numbered that gives each price its row_num, lowest first, and the total number of prices. Then select AVG(price) as median from it, keeping only the middle row or two.

Assignment
Follow the Coding Tutorial to build the median query, one clause per line.

Hint
Before the last step your query prints 18.25: that is the mean of all 8 prices. The middle rows are (total + 1) / 2 and (total + 2) / 2: rows 4 and 5 when there are 8 prices, row 5 twice when there are 9.