Tags

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


SQL Puzzle | The Maximum Consecutive Numbers Puzzle

Original Puzzle link – https://en.juffalow.com/sql-puzzles

Consider we have a table with two columns, The first column is an identity column and the second is flag with values 0 and 1. Here you have to write the SQL to get number of the same consecutive flag numbers.

So the output from the example should be :

2 — We have 2 consecutive 0’s
3 — then we have 3 consecutive 1’s
2 — then 2 consecutive 0’s & so on….
4

Please check out the sample input and the expected output below-

Sample Input

dt Rate
1 0
2 0
6 0
7 0
3 1
4 1
5 1
8 1
9 1
10 1
11 1

Expected Output

Counts
2
3
2
4

Script

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

--


CREATE TABLE [dbo].[testZero]
(
[dt] INT NULL,
[Rate] [int] NULL
)
GO
 
--Insert Data
INSERT INTO [dbo].[testZero](dt,Rate)
VALUES
(1,0),
(2,0),
(3,1),
(4,1),
(5,1),
(6,0),
(7,0),
(8,1),
(9,1),
(10,1),
(11,1)
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


--

;WITH CTE1 AS
( 
       SELECT dt , Rate , ROW_NUMBER() OVER (ORDER BY dt) Rnk  FROM [testZero] 
)
,CTE2 AS
(
       SELECT *,  CASE WHEN Rate = lag(Rate) over(order by rnk) THEN 0 ELSE 1 END cols FROM CTE1 c2
)
,CTE3 AS
(
       SELECT *,  SUM(cols) over(order by rnk) Grouper FROM CTE2 c2
)
SELECT (MAX(dt) - MIN(dt)) + 1 Counts FROM CTE3 GROUP BY Grouper

--

Solution 2


--

;WITH CTE AS
(
	SELECT   dt
			,Rate
			,dt - ROW_NUMBER() OVER (PARTITION BY Rate ORDER BY dt) RN 
        FROM [testZero]
)
SELECT  
		(MAX(dt) - MIN(dt)) + 1 Counts 
FROM 
		CTE 
GROUP BY 
		RN, Rate
ORDER BY 
		rn,Rate

--

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

Advertisements