SQL Puzzle | The Maximum Consecutive Numbers Puzzle

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”.

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