Tags

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


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

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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

1 https://msbiskills.com/2016/09/09/sql-puzzle-the-five-consecutive-dates-puzzle/
2 https://msbiskills.com/2016/06/10/sql-puzzle-the-consecutive-groups-count-puzzle/
3 https://msbiskills.com/2016/06/04/sql-puzzle-the-maximum-count-of-consecutive-numbers-puzzle/
4 https://msbiskills.com/2015/10/05/t-sql-query-replace-6-or-more-consecutive-digits-with-x-from-a-string-puzzle/
5 https://pawankkmr.wordpress.com/2015/05/06/t-sql-query-the-consecutive-wins-puzzle-2/
6 https://pawankkmr.wordpress.com/2015/03/24/t-sql-query-consecutive-wins-for-india-puzzle/

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