[HackerRank][SQL(Oracle)] SQL Project Planning

 

※  You are given a table, Projects, containing three columns: Task_ID, Start_Date and End_Date.

It is guaranteed that the difference between the End_Date and the Start_Date is equal to 1 day for each row in the table.

If the End_Date of the tasks are consecutive, then they are part of the same project. Samantha is interested in finding the total number of different projects completed.

 

■  [Advanced Join]  SQL Project Planning

https://www.hackerrank.com/challenges/sql-projects/problem?isFullScreen=true 

Q.

Write a query to output the start and end dates of projects listed by the number of days it took to complete the project in ascending order. If there is more than one project that have the same number of completion days, then order by the start date of the project.

A.

SELECT 		P_START_DATE, MAX(END_DATE) AS P_END_DATE
FROM 		(
				SELECT 		START_DATE AS P_START_DATE, CONNECT_BY_ROOT END_DATE AS END_DATE --, LEVEL
				FROM 		PROJECTS P1
				WHERE 		NOT EXISTS (SELECT NULL FROM PROJECTS P2 WHERE P1.START_DATE = P2.END_DATE)
				CONNECT BY PRIOR START_DATE = END_DATE
			)
GROUP BY 	P_START_DATE
ORDER BY 	P_END_DATE - P_START_DATE, P_START_DATE;
-- ORDER BY TO_DATE(P_END_DATE,'YYYY-MM-DD') - TO_DATE(P_START_DATE,'YYYY-MM-DD') + 1, P_START_DATE;

SELECT 		MIN(START_DATE) AS P_START_DATE, P_END_DATE
FROM 		(
				SELECT 		CONNECT_BY_ROOT START_DATE AS START_DATE, END_DATE AS P_END_DATE --, LEVEL
				FROM 		PROJECTS P1
				WHERE 		NOT EXISTS (SELECT NULL FROM PROJECTS P2 WHERE P2.START_DATE = P1.END_DATE)
				CONNECT BY PRIOR END_DATE = START_DATE
			)
GROUP BY 	P_END_DATE
ORDER BY 	P_END_DATE - P_START_DATE, P_START_DATE;

( https://docs.oracle.com/database/121/SQLRF/queries003.htm#SQLRF52335 )

(+)

SELECT 		MIN(START_DATE) AS P_START_DATE
			,MAX(END_DATE) AS P_END_DATE
FROM 		(
				SELECT 	P.*
						,START_DATE - DENSE_RANK() OVER (ORDER BY START_DATE) AS ID	-- TO_DATE(START_DATE,'YYYY-MM-DD')
				FROM 	PROJECTS P
			)
GROUP BY 	ID
ORDER BY 	P_END_DATE - P_START_DATE, P_START_DATE;
-- ORDER BY TO_DATE(P_END_DATE,'YYYY-MM-DD') - TO_DATE(P_START_DATE,'YYYY-MM-DD'), P_START_DATE;
-- ORDER BY DATEDIFF(P_END_DATE, P_START_DATE), P_START_DATE;

SELECT 		MIN(START_DATE) AS P_START_DATE
			,MAX(END_DATE) AS P_END_DATE
FROM 		(
				SELECT 	P.*
						,SUM(CASE WHEN START_DATE IN (SELECT END_DATE FROM PROJECTS) THEN 0 ELSE 1 END)
							OVER (ORDER BY START_DATE) AS ID
				FROM 	PROJECTS P
			)
GROUP BY 	ID
ORDER BY 	COUNT(ID), P_START_DATE;

SELECT 		P_START_DATE, P_END_DATE
FROM 		(
			SELECT 		MIN(START_DATE) AS P_START_DATE
						,MAX(END_DATE) AS P_END_DATE
						,COUNT(ID) AS TERM
			FROM 		(
							SELECT 	P.*
									,SUM(CASE WHEN EXISTS (SELECT 1 FROM PROJECTS P2 WHERE P.START_DATE = P2.END_DATE) THEN 0 ELSE 1 END)
										OVER (ORDER BY START_DATE) AS ID
							FROM 	PROJECTS P
						)
			GROUP BY 	ID
)
ORDER BY 	TERM, P_START_DATE;

SELECT 		P_START_DATE, P_END_DATE
FROM 		(
				SELECT 	START_DATE AS P_START_DATE, ROW_NUMBER() OVER (ORDER BY START_DATE) AS RN
				FROM 	PROJECTS P
				WHERE 	NOT EXISTS (SELECT 1 FROM PROJECTS P2 WHERE P.START_DATE = P2.END_DATE)
			) P_S
			JOIN (
				SELECT 	END_DATE AS P_END_DATE, ROW_NUMBER() OVER (ORDER BY END_DATE) AS RN
				FROM 	PROJECTS P
				WHERE 	NOT EXISTS (SELECT 1 FROM PROJECTS P2 WHERE P.END_DATE = P2.START_DATE)
			) P_E
			ON 	P_S.RN = P_E.RN
ORDER BY 	P_END_DATE - P_START_DATE, P_START_DATE;

 

* DateAdd() ( https://docs.oracle.com/cd/E41183_01/DR/Dateadd.html )

* Datadiff() ( https://docs.oracle.com/cd/E57185_01/ESBTR/mdx_datediff.html )

* EXISTS ( https://www.w3schools.com/sql/sql_exists.asp )

* IN vs EXISTS ( https://www.javatpoint.com/in-vs-exists )