Find Median Given Frequency Of Numbers Problem


Description

LeetCode Problem 571.

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

1
2
3
4
5
6
7
8
+----------+-------------+
|  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.

1
2
3
4
5
+--------+
| median |
+--------|
| 0.0000 |
+--------+

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


MySQL Solution

1
2
3
4
5
6
select round(sum(Number) / count(Number), 2) as median
from (select Number, Frequency,
      sum(Frequency) over (order by Number) as cumulative_num,
      sum(Frequency) over () as total_num
      from Numbers) sub
where total_num / 2.0 between cumulative_num - Frequency and cumulative_num




Related Posts

Find Median Given Frequency Of Numbers Problem

LeetCode 571. Write a query to find the median of...

Find Cumulative Salary Of An Employee Problem

LeetCode 579. Write a SQL to get the cumulative sum...

Get Highest Answer Rate Question Problem

LeetCode 578. Write a sql query to identify the question...

Winning Candidate Problem

LeetCode 574. Write a sql to find the name of...

Managers With At Least 5 Direct Reports Problem

LeetCode 570. Given the Employee table, write a SQL query...

Employee Bonus Problem

LeetCode 577. Select all employee’s name and bonus whose bonus...