Tags
Complex SQL Challenges, complex sql statement(puzzle), Complex TSQL Challenge, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Khowal, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, puzzle sql developer, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Interview Questions, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL Quiz, SQL Server Database, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQL Top clause, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, T SQL Puzzles, T-SQL Challenge, T-SQL Query | [ The Complex Week Puzzle ], The Biggest Gap Puzzle, The Gap Puzzle, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries, Week puzzle
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.”
- The puzzle is very cool.
- You have to find out the median from the integer input data.
- 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
Improving my SQL BI Skills 
;WITH CTE
AS
(
SELECT Value,ROW_NUMBER() OVER(ORDER BY Value) [Ascend],ROW_NUMBER() OVER(ORDER BY Value desc) [Descend]
FROM Median
)
SELECT AVG(Value)
FROM CTE
WHERE Ascend IN (Descend,Descend-1,Descend+1)
LikeLike
;WITH CTE
AS
(
SELECT Value,
ROW_NUMBER() OVER(ORDER BY Value) [Ascend],
ROW_NUMBER() OVER(ORDER BY Value desc) [Descend]
FROM Median
)
SELECT AVG(Value)
FROM CTE
WHERE Ascend IN (Descend,Descend-1,Descend+1)
LikeLike