Daily Archives: May 10, 2015

T-SQL Query | [ The Tree Puzzle ]

10 Sunday May 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ 3 Comments

Tags

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


T-SQL Query | [ The Tree Puzzle ]

  1. We have a table called Hierarchies, here we have two columns named ParentID and ChildID. Each ChildID can appear again in this table as a Parent, with other children. Its like a tree structure.
  2. Please check out the sample input and expected output for details.

Sample Input

ParentID ChildID
1 2
2 3
3 4

Expected Output

ParentID ChildID
1 2
1 3
1 4
2 3
2 4
3 4

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 Hierarchies
(
	  ParentID INT
	, ChildID INT
)
GO

INSERT Hierarchies VALUES (1,2)
INSERT Hierarchies VALUES (2,3)
INSERT Hierarchies VALUES (3,4)

—

Update May 10 | Solutions – Pawan Kumar Khowal 


--

;WITH CTE1 AS
(
	SELECT ParentID , ChildID FROM Hierarchies
	UNION ALL
	SELECT c.ParentID , H.ChildID FROM CTE1 c INNER JOIN Hierarchies H
	ON H.ParentID = c.ChildID 
)
SELECT * FROM CTE1 ORDER BY ParentID



--

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

Keep Learning

Http://MSBISkills.com

Pawan Kumar Khowal

T-SQL Query | [ The Previous Value(Non Null) Puzzle ]

10 Sunday May 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ Leave a comment

Tags

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


T-SQL Query | [ The Previous Value(Non Null) Puzzle ]

  1. The Puzzle is simple. This puzzle is part two of the first puzzle I wrote.
  2. You have to find previous non null value from the value column.
  3. Please check out the sample input and expected output for details.

Sample Input

Id Value
12 NULL
31 100
52 -72
77 NULL
111 17
131 NULL
174 NULL
190 NULL
231 34

Expected Output

Id Value PrevNonNullValue
12 NULL NULL
31 100 100
52 -72 -72
77 NULL -72
111 17 17
131 NULL 17
174 NULL 17
190 NULL 17
231 34 34

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 PrevValue
(
   Id INT
  ,Value INT NULL
)
GO

INSERT INTO PrevValue VALUES
( 12, NULL),
( 31, 100),
( 52, -72),
( 77, NULL),
(111, 17),
(131, NULL),
(174, NULL),
(190, NULL),
(231, 34)

—

Update May 10 | Solutions – Pawan Kumar Khowal 


--

;WITH CTE AS
(
	SELECT * , ROW_NUMBER() OVER (ORDER BY %%Physloc%%) rnk FROM PrevValue
)
SELECT Id,Value
	,(SELECT TOP 1 Value from CTE d WHERE d.rnk = 
		(SELECT MAX(e.rnk) FROM CTE e WHERE e.rnk <= c.rnk AND e.Value IS NOT NULL)) 
PrevNonNullValue			
FROM CTE c

--

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

Keep Learning

Http://MSBISkills.com

Pawan Kumar Khowal

T-SQL Query | [ The Median Puzzle ]

10 Sunday May 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ 2 Comments

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

T-SQL Query | [ The Average Marks Puzzle ]

10 Sunday May 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ 1 Comment

Tags

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


T-SQL Query | [ The Average Marks Puzzle ]

Puzzle Statement

  1. The puzzle is simple.
  2. You have to list out student information where the student scored more than the Average Marks per subject.
  3. Please check out the sample input and expected output for details.

Sample Input

Sname SMarks SSubject
A 10 X
B 20 X
C 30 Y
D 40 Y

Expected Output

Sname SMarks SSubject
B 20 X
D 40 Y

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 Neeraj
(
       Sname VARCHAR(1)
       ,SMarks INT
       ,SSubject VARCHAR(1) 
)
GO

INSERT INTO Neeraj(Sname, SMarks , SSubject) VALUES 
('A'   ,       10   ,  'X'),
('B'   ,         20  , 'X'),
('C'   ,         30  ,  'Y'),
('D',     40  ,  'Y')

—

Update May 10 | Solutions — Pawan Kumar Khowal 


--

/************   SOLUTION 1         ****************/

SELECT n.*, Ag FROM Neeraj n
INNER JOIN (
SELECT AVG(SMarks) Ag , SSubject FROM Neeraj GROUP BY SSubject ) a on a.SSubject = n.SSubject
WHERE SMarks > Ag



/************   SOLUTION 2        ****************/

SELECT * 
FROM Neeraj n 
WHERE SMarks > ( SELECT AVG(SMarks) ag FROM Neeraj n1 WHERE n1.SSubject = n.SSubject GROUP BY SSubject ) 


--

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

Keep Learning

Http://MSBISkills.com