Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ The Median Puzzle ]

Best post on Median – http://sqlperformance.com/2012/08/t-sql-queries/median

What is a Median

As per http://en.wikipedia.org/wiki/Median, “The median is the numerical value separating the higher half of a data sample, a population, or a probability distribution, from the lower half.”

  1. The puzzle is very cool.
  2. You have to find out the median from the integer input data.
  3. Please check out the sample input and expected output for details.

Sample Input

Value
1
3
7
17
23
42

Expected Output

Median
12

 

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


CREATE TABLE Median
(
	Value INT
)
GO

INSERT INTO Median
VALUES (1),(3),(7),(17),(23),(42)


Update May 10 | Solutions – Pawan Kumar Khowal


--

/************ SOLUTION 0 - Pawan Kumar Khowal *********************/


;WITH CTE AS
(
	SELECT Value,ROW_NUMBER() OVER (ORDER BY Value) rnk
	FROM Median
)
SELECT SUM(Value)/2.0 Median 
FROM CTE WHERE rnk BETWEEN (SELECT (COUNT(*)+1)/2 FROM Median) AND (SELECT (COUNT(*)+2)/2 FROM Median)



/************ SOLUTION 1 - Using Analytical Function *********************/


SELECT TOP 1 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Value) OVER () Median
FROM dbo.Median



/************ SOLUTION 2 - Aaron Bertrand *********************/


;WITH CTE AS
(
	SELECT Value, COUNT(*) OVER () rnk1 ,ROW_NUMBER() OVER (ORDER BY value DESC) rnk2
	FROM Median
)
SELECT SUM(Value) / 2. Median  
FROM CTE WHERE rnk2 BETWEEN (rnk1 + 1) / 2  AND (rnk1+2) / 2



/************ SOLUTION 3 - Aaron Bertrand *********************/


;WITH CTE AS
(
	SELECT Value,ROW_NUMBER() OVER (ORDER BY value) rnk1 ,ROW_NUMBER() OVER (ORDER BY value DESC) rnk2
	FROM Median
)
SELECT SUM(Value) / 2. Median  
FROM CTE WHERE rnk1 >= rnk2 - 1 AND rnk1 <= rnk2 + 1 



/************ SOLUTION 4 - Itzik Ben-Gan  *********************/

SELECT AVG(1.0 * value) Median
FROM (
     SELECT value FROM dbo.Median
     ORDER BY value
     OFFSET ((SELECT COUNT(*) FROM Median) - 1) / 2 ROWS
     FETCH NEXT 1 + (1 - (SELECT COUNT(*) FROM Median) % 2) ROWS ONLY
) AS x;

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

Http://MSBISkills.com

Pawan Kumar Khowal

Advertisements