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

Name LEVEL
A 1
A 2
A 3
B 4
B 10
A 11
A 15
B 16
C 20
C 21
A 22
A 23

Expected Output

Name StartPosition EndPosition
A 1 3
A 11 15
B 4 10
A 22 23
B 16 16
C 20 21

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