571. Find Median Given Frequency of Numbers

The Numbers table keeps the value of number and its frequency.

+----------+-------------+
| Number | Frequency |
+----------+-------------|
| 0 | 7 |
| 1 | 1 |
| 2 | 3 |
| 3 | 1 |
+----------+-------------+

In this table, the numbers are 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3, so the median is (0 + 0) / 2 = 0.

+--------+
| median |
+--------|
| 0.0000 |
+--------+

Write a query to find the median of all numbers and name the result as median.

with recursive cte as
(
select number, frequency from Numbers
union all
select number, frequency -1 from cte where frequency > 1
)select avg(number) as median
from
(
select number, rank() over(order by number, frequency) as ranka,
rank() over(order by number desc, frequency desc) as rankd
from cte order by 1
) x
where ranka = rankd or ranka = rankd + 1 or ranka = rankd - 1

Link

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Isabelle

Isabelle

In love with telling stories with data