365 Days of SQL: Day 144

I took a day off today cause I had some personal things to run. I am super happy with my progress today and in general. I spent my day by relaxing and simply enjoying a nice Netflix movie called “Happiness”. I have recently started this challenge to post continuously in LinkedIn and I am just wondering what I should post about today.

Puzzle #29: Count the Groupings

I tried the solution in the live oracle environment here.

I relied on the solution from the site to solve the puzzle.

WITH temp AS 
(SELECT 1 AS StepNumber,'Test Case 1' AS TestCase,'Passed' AS Status FROM DUAL UNION ALL
SELECT 2 AS StepNumber,'Test Case 2' AS TestCase,'Passed' AS Status FROM DUAL UNION ALL
SELECT 3 AS StepNumber,'Test Case 3' AS TestCase,'Passed' AS Status FROM DUAL UNION ALL
SELECT 4 AS StepNumber,'Test Case 4' AS TestCase,'Passed' AS Status FROM DUAL UNION ALL
SELECT 5 AS StepNumber,'Test Case 5' AS TestCase,'Failed' AS Status FROM DUAL UNION ALL
SELECT 6 AS StepNumber,'Test Case 6' AS TestCase,'Failed' AS Status FROM DUAL UNION ALL
SELECT 7 AS StepNumber,'Test Case 7' AS TestCase,'Failed' AS Status FROM DUAL UNION ALL
SELECT 8 AS StepNumber,'Test Case 8' AS TestCase,'Failed' AS Status FROM DUAL UNION ALL
SELECT 9 AS StepNumber,'Test Case 9' AS TestCase,'Failed' AS Status FROM DUAL UNION ALL
SELECT 10 AS StepNumber,'Test Case 10' AS TestCase,'Passed' AS Status FROM DUAL UNION ALL
SELECT 11 AS StepNumber,'Test Case 11' AS TestCase,'Passed' AS Status FROM Dual UNION ALL
SELECT 12 AS StepNumber,'Test Case 12' AS TestCase,'Passed' AS Status FROM Dual),
cte_RowNumber AS
(
SELECT	Status,
		StepNumber,
		ROW_NUMBER() OVER (PARTITION BY Status ORDER BY StepNumber) AS RowNumber,
		StepNumber - ROW_NUMBER() OVER (PARTITION BY Status ORDER BY StepNumber) AS Rnk
FROM	temp
ORDER BY StepNumber
)

SELECT	ROW_NUMBER() OVER (ORDER BY Rnk) AS StepOrder,
		Status,
		MAX(StepNumber) - MIN(StepNumber) + 1 AS ConsecutiveCount
FROM	cte_RowNumber
GROUP BY Rnk, Status
ORDER BY Rnk, Status;