SQL Puzzle | The Sum of maximum consecutive values puzzle (1)

In this puzzle you have to group by data based on the Id and generate two new columns, Count for each id + 1 and the sum of the maximum consecutive Vals. For more details please check the sample input and expected output.

Sample Input

Id Vals
p 1
p 0
p 1
p 1
p 1
p 0
q 1
q 1
q 0
q 1
q 0
q 0

Expected Output

Id NextValue MaximumConsecutiveValues
p 7 3
q 7 2

Script

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

 ```-- CREATE TABLE ConsecutivePuzzle1 ( Id VARCHAR(5) ,Vals INT /* Value can be 0 or 1 */ ) GO INSERT INTO ConsecutivePuzzle1 VALUES ('p', 1), ('p', 0), ('p', 1), ('p', 1), ('p', 1), ('p', 0), ('q', 1), ('q', 1), ('q', 0), ('q', 1), ('q', 0), ('q', 0) GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution – 1 /*Order by (SELECT 1) is not very proper, you should use some Primary Key or Id column*/

 ```-- SELECT Id, COUNT(*) + 1 NextValue , SUM(rnk) MaximumConsecutiveValues FROM ( SELECT *, ISNULL(LEAD(Vals) OVER (PARTITION BY Id ORDER BY (SELECT 1)),0) rnk from ConsecutivePuzzle1 )m GROUP BY Id -- ```

Output-1

 ```-- /*------------------------ SELECT Id, COUNT(*) + 1 NextValue , SUM(rnk) MaximumConsecutiveValues FROM ( SELECT *, ISNULL(LEAD(Vals) OVER (PARTITION BY Id ORDER BY (SELECT 1)),0) rnk from ConsecutivePuzzle1 )m GROUP BY Id ------------------------*/ Id NextValue MaximumConsecutiveValues ----- ----------- ------------------------ p 7 3 q 7 2 (2 row(s) affected) -- ```

Solution – 2

 ```-- ;WITH CTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY (SELECT 1)) rnk from ConsecutivePuzzle1 ) SELECT t1.Id ,MAX(t2.rnk)+1 NextValue ,MAX(t2.rnk - t1.rnk) MaximumConsecutiveValues FROM CTE t1 INNER JOIN CTE t2 ON t1.Id = t2.Id AND t1.rnk < t2.rnk AND t1.Vals = t2.Vals GROUP BY t1.Id -- ```

Output-2

 ```-- /*------------------------ ;WITH CTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY (SELECT 1)) rnk from ConsecutivePuzzle1 ) SELECT t1.Id ,MAX(t2.rnk)+1 NextValue ,MAX(t2.rnk - t1.rnk) MaximumConsecutiveValues FROM CTE t1 INNER JOIN CTE t2 ON t1.Id = t2.Id AND t1.rnk < t2.rnk AND t1.Vals = t2.Vals GROUP BY t1.Id ------------------------*/ Id NextValue MaximumConsecutiveValues ----- -------------------- ------------------------ p 7 3 q 7 2 (2 row(s) affected) -- ```

Other Reference Puzzles for Consecutive things

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