T-SQL Query | [ The GAP Puzzle ]

Puzzle Statement

1. I got this puzzle on the web. The Puzzle posted by Jim Geissman.
2. Here we have to locate the largest gap, and compute the sum of N for
   the cases on either side. There are multiple locations, multiple
   Eastings per location, but only one largest gap. (If there are two largest
   gaps, it does’t matter which one is used for the sums.
3. In case of CA the largest gap is 6 [ Difference between 103 and 109 ], and the sum of N for the 2 rows below the gap is 8, and for the 2 above the gap it’s 5.
4. Please check out the sample input and expected output for details.

Sample Input

Expected Output

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 a11(Loc CHAR(2), East INT, N INT) 
go 
INSERT a11 VALUES('CA', 100, 3) 
INSERT a11 VALUES('CA', 103, 5) 
INSERT a11 VALUES('CA', 109, 2) 
INSERT a11 VALUES('CA', 110, 3) 
INSERT a11 VALUES('OR', 100, 3) 
INSERT a11 VALUES('OR', 108, 5) 
INSERT a11 VALUES('OR', 109, 2) 
INSERT a11 VALUES('OR', 110, 3) 
INSERT a11 VALUES('WA', 108, 5) 
INSERT a11 VALUES('WA', 109, 2) 
INSERT a11 VALUES('WA', 110, 3) 
INSERT a11 VALUES('WA', 115, 3) 

Update May 8 | Solution 1 – Pawan Kumar Khowal

--


;WITH CTE1 AS
(
	SELECT * , EAST - LAG(EAST) OVER (PARTITION BY loc ORDER BY EAST) MaxGapSize	
	,SUM(N) OVER (PARTITION BY loc ORDER BY EAST DESC) AfterGap	
	,( SELECT SUM(N) FROM a11 WHERE a.Loc = a11.Loc AND a.East > a11.East ) GapStart
	FROM a11 a	
)
SELECT Loc , GapStart , AfterGap , MaxGapSize
FROM CTE1 a
WHERE MaxGapSize = ( SELECT MAX(MaxGapSize) FROM CTE1 WHERE a.Loc = CTE1.Loc ) 


--

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

Keep Learning

Http://MSBISkills.com

Pawan Kumar Khowal