Leetcode SQL

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.

Solution:

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