Tags

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


SQL Puzzle | SQL Puzzle | Generate ranking for groups and within groups

In this puzzle you have to generate 2 new columns called Grouper and InternalGroups. For more details please check the sample input and expected output.

Sample Input

id FirstNumber NextNumber Dates
A1 123 231 2017-12-14 19:04:03.950
A1 231 411 2017-12-15 19:04:03.950
A1 411 NULL 2017-12-16 19:04:03.950
A1 111 109 2017-12-17 19:04:03.950
A1 109 891 2017-12-18 19:04:03.950
A1 891 144 2017-12-19 19:04:03.950
A1 144 151 2017-12-20 19:04:03.950
A1 151 NULL 2017-12-21 19:04:03.950
A2 118 987 2017-12-21 19:04:03.950
A2 987 765 2017-12-22 19:04:03.950
A2 765 NULL 2017-12-23 19:04:03.950

Screen Shot

Expected Output

id FirstNumber NextNumber dates Grouper InternalGroups
A1 123 231 2017-12-14 19:04:03.950 1 1
A1 231 411 2017-12-15 19:04:03.950 1 2
A1 411 NULL 2017-12-16 19:04:03.950 1 3
A1 111 109 2017-12-17 19:04:03.950 2 1
A1 109 891 2017-12-18 19:04:03.950 2 2
A1 891 144 2017-12-19 19:04:03.950 2 3
A1 144 151 2017-12-20 19:04:03.950 2 4
A1 151 NULL 2017-12-21 19:04:03.950 2 5
A2 118 987 2017-12-21 19:04:03.950 1 1
A2 987 765 2017-12-22 19:04:03.950 1 2
A2 765 NULL 2017-12-23 19:04:03.950 1 3

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE GenerateSequence
(
	    [id] VARCHAR(8)
	  , [FirstNumber] BIGINT
	  , [NextNumber] BIGINT
)
GO

INSERT INTO GenerateSequence VALUES 

('A1',123,231),
('A1',231,411),
('A1',411,NULL),

('A1',111,109),
('A1',109,891),
('A1',891,144),
('A1',144,151),
('A1',151,NULL), 

('A2',118,987),
('A2',987,765),
('A2',765,NULL)
GO

--

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

Solution – 1

--

SELECT 
  Id,FirstNumber,[NextNumber],Dates
, CASE WHEN [NextNumber] IS NULL THEN Grouper - 1 ELSE Grouper END Grouper 
, ROW_NUMBER() OVER(PARTITION BY id,CASE WHEN [NextNumber] IS NULL THEN Grouper-1 ELSE Grouper END ORDER BY rnk) InternalGroups
from 
(
 select
	  *,
	  SUM(seq) over(PARTITION BY id ORDER BY rnk)+1 Grouper
	FROM 
	(
		SELECT
		*
		, ROW_NUMBER() OVER(PARTITION BY [id] ORDER BY dates) rnk	
		, CASE WHEN [NextNumber] IS NULL THEN 1 ELSE 0 END seq			
		FROM GenerateSequence
	)p
)c
ORDER BY Id,Dates

--

Output-1

--

/*------------------------
SELECT 
  Id,FirstNumber,[NextNumber],Dates
, CASE WHEN [NextNumber] IS NULL THEN Grouper - 1 ELSE Grouper END Grouper 
, ROW_NUMBER() OVER(PARTITION BY id,CASE WHEN [NextNumber] IS NULL THEN Grouper-1 ELSE Grouper END ORDER BY rnk) InternalGroups
from 
(
 select
	  *,
	  SUM(seq) over(PARTITION BY id ORDER BY rnk)+1 Grouper
	FROM 
	(
		SELECT
		*
		, ROW_NUMBER() OVER(PARTITION BY [id] ORDER BY dates) rnk	
		, CASE WHEN [NextNumber] IS NULL THEN 1 ELSE 0 END seq			
		FROM GenerateSequence
	)p
)c
ORDER BY Id,Dates
------------------------*/
Id       FirstNumber          NextNumber           Dates                   Grouper     InternalGroups
-------- -------------------- -------------------- ----------------------- ----------- --------------------
A1       123                  231                  2017-12-14 19:04:03.950 1           1
A1       231                  411                  2017-12-15 19:04:03.950 1           2
A1       411                  NULL                 2017-12-16 19:04:03.950 1           3
A1       111                  109                  2017-12-17 19:04:03.950 2           1
A1       109                  891                  2017-12-18 19:04:03.950 2           2
A1       891                  144                  2017-12-19 19:04:03.950 2           3
A1       144                  151                  2017-12-20 19:04:03.950 2           4
A1       151                  NULL                 2017-12-21 19:04:03.950 2           5
A2       118                  987                  2017-12-21 19:04:03.950 1           1
A2       987                  765                  2017-12-22 19:04:03.950 1           2
A2       765                  NULL                 2017-12-23 19:04:03.950 1           3

(11 row(s) affected)



--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com