Welcome to Subscribe On Youtube

571. Find Median Given Frequency of Numbers

Description

Table: Numbers

+-------------+------+
| Column Name | Type |
+-------------+------+
| num         | int  |
| frequency   | int  |
+-------------+------+
num is the primary key (column with unique values) for this table.
Each row of this table shows the frequency of a number in the database.

 

The median is the value separating the higher half from the lower half of a data sample.

Write a solution to report the median of all the numbers in the database after decompressing the Numbers table. Round the median to one decimal point.

The result format is in the following example.

 

Example 1:

Input: 
Numbers table:
+-----+-----------+
| num | frequency |
+-----+-----------+
| 0   | 7         |
| 1   | 1         |
| 2   | 3         |
| 3   | 1         |
+-----+-----------+
Output: 
+--------+
| median |
+--------+
| 0.0    |
+--------+
Explanation: 
If we decompress the Numbers table, we will get [0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3], so the median is (0 + 0) / 2 = 0.

Solutions

• SQL

• # Write your MySQL query statement below
  WITH
      t AS (
          SELECT
              *,
              SUM(frequency) OVER (ORDER BY num ASC) AS rk1,
              SUM(frequency) OVER (ORDER BY num DESC) AS rk2,
              SUM(frequency) OVER () AS s
          FROM Numbers
      )
  SELECT
      ROUND(AVG(num), 1) AS median
  FROM t
  WHERE rk1 >= s / 2 AND rk2 >= s / 2;
  
  

All Problems

All Solutions