365 Days of Daily Coding: Day 143

“I am quite tired today. I just arrived home after celebrating my friend’s birthday. We went to a nice restaurant called “Blonde”. They sell fusion foods. I had BBQ lamb kebab that was served with rice. I would say it was ok. The meat was sort of caramelised and I normally do not like my food sweet. “

I was supposed to post this ystd but I was so tried that I just went to sleep so here I am continuing yesterday’s blog. One thing I need to learn more about is the correlated subqueries and their application.

Puzzle #28 : Fill the Gaps

I tried the solution in the oracle environment here. The solution are taken from the advanced SQL git repo here.

WITH temp AS 
(SELECT 1 AS ROW_NUMBER, 'Alpha' AS Workflow, 'Pass' AS Status FROM DUAL UNION ALL
SELECT 2 AS ROW_NUMBER, '' AS Workflow, 'Fail' AS Status FROM DUAL UNION ALL
SELECT 3 AS ROW_NUMBER, '' AS Workflow, 'Fail' AS Status FROM DUAL UNION ALL
SELECT 4 AS ROW_NUMBER, '' AS Workflow, 'Fail' AS Status FROM DUAL UNION ALL
SELECT 5 AS ROW_NUMBER, 'Bravo' AS Workflow, 'Pass' AS Status FROM DUAL UNION ALL
SELECT 6 AS ROW_NUMBER, '' AS Workflow, 'Fail' AS Status FROM DUAL UNION ALL
SELECT 7 AS ROW_NUMBER, '' AS Workflow, 'Fail' AS Status FROM DUAL UNION ALL
SELECT 8 AS ROW_NUMBER, '' AS Workflow, 'Pass' AS Status FROM DUAL UNION ALL
SELECT 9 AS ROW_NUMBER, '' AS Workflow, 'Pass' AS Status FROM DUAL
)

-- Solution 1
SELECT	a.Row_Number,
		(SELECT	 b.Workflow
		 FROM	 temp b
		 WHERE	 b.RowNumber =
					 (SELECT MAX(c.Row_Number)
					 FROM temp c
					 WHERE c.Row_Number <= a.Row_Number AND c.Workflow != '')) Workflow
FROM temp a;

--Solution 2
SELECT	Row_Number,
		MAX(Workflow) OVER (PARTITION BY DistinctCount) AS TestCase
FROM	(SELECT	Row_Number,
				Workflow,
				COUNT(Workflow) OVER (ORDER BY Row_Number) AS DistinctCount
		FROM temp) a
ORDER BY Row_Number;