SQL Puzzle | The Gap Puzzle – V

The requirement is you have to write a SQL statements to achieve the desired result. Here we have to use T-SQL to find the sequencing of groups for each Name. The field LEVEL is unique but we may have gaps in the number list.

E.g.

1. The name “A” first sequence group is for LEVEL 1,2,3 – its sequence 2 is for LEVEL 11,15 and its sequence 3 is for LEVEL 22,23.

2. The “B” name sequence group 1 contains LEVEL 4,10 and its sequence 2 is for LEVEL 16.

3. For “C” we have only one sequence group, for LEVEL 20,21

For details please check out the sample input and the expected output below-

Sample Inputs

Expected Output

Rules/Restrictions

• The challenge is to do it with T-SQL statements only.
• CTEs are allowed.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data

--


CREATE TABLE TheGapPuzzle
(
   Name VARCHAR(1)
  ,LEVEL integer
)
GO

INSERT INTO TheGapPuzzle VALUES
('A',  1),
('A',  2),
('A',  3),
('B',  4),
('B',  10),
('A',  11),
('A',  15),
('B',  16),
('C',  20),
('C',  21),
('A',  22),
('A',  23)
GO

SELECT * FROM TheGapPuzzle
GO

--

—

Solution 1

/*
** Solution 1
*/

SELECT
Name
, MIN(LEVEL) StartPosition
, MAX(LEVEL) EndPosition
FROM
(
SELECT
Name
, LEVEL
, ROW_NUMBER() OVER(ORDER BY LEVEL) - ROW_NUMBER() OVER(PARTITION BY Name ORDER BY (SELECT 1)) rnk
FROM THEGapPuzzle
)r
GROUP BY Name,rnk

--

Solution 2

--

/*
** Solution 2
*/

SELECT Name, StartPosition, EndPosition FROM
(
SELECT Name,LeadValue, LagValue,level StartPosition, CASE (LEAD(name) OVER (ORDER BY ( select 1))) WHEN name THEN LEAD(level) OVER (ORDER BY ( select 1)) ELSE level END EndPosition
FROM
(

SELECT
name
,CASE (LAG(name) OVER (ORDER BY ( select 1 ))) WHEN name THEN 1 ELSE 0 END LagValue
,CASE (LEAD(name) OVER (ORDER BY ( select 1))) WHEN name THEN 1 ELSE 0 END LeadValue
,level
FROM TheGapPuzzle

) tbl2
WHERE tbl2.LagValue = 0 OR tbl2.LeadValue = 0
) tbl3
WHERE LagValue=0


--

Solution 3

--

/*
** Solution 3
*/

SELECT
Name
,MIN(Level) StartPosition
,MAX(Level) EndPosition
FROM
(
SELECT Name,LEVEL , SUM(gap) OVER (ORDER BY LEVEL) grp From
(
SELECT
Name
,LEVEL
,CASE WHEN Name = Lag(Name,1,0) OVER (ORDER BY (SELECT 1)) THEN 0 ELSE 1 END gap
FROM TheGapPuzzle
)
DT
)t
GROUP BY Name,grp

--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com