- Prepare
- SQL
- Advanced Join
- SQL Project Planning
- Discussions
SQL Project Planning
SQL Project Planning
+ 70 comments A simple MySQL implementation without variables and only requiring DATEDIFF():
SET sql_mode = ''; SELECT Start_Date, End_Date FROM (SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) a, (SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) b WHERE Start_Date < End_Date GROUP BY Start_Date ORDER BY DATEDIFF(End_Date, Start_Date), Start_Date
Edits: Updated with the sql_mode='' based on @jakab922's comment. HackerRank changed the default sql_mode behavior to only_full_group_by at some point after I posted this.
Also, a full solution without the need to change modes was posted by @dougal_michael below, which uses the MIN() function instead.
+ 14 comments Just adding this commented version. MySQL
SELECT Start_Date, MIN(End_Date) FROM /* Choose start dates that are not end dates of other projects (if a start date is an end date, it is part of the samee project) */ (SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) a, /* Choose end dates that are not end dates of other projects */ (SELECT end_date FROM PROJECTS WHERE end_date NOT IN (SELECT start_date FROM PROJECTS)) b /* At this point, we should have a list of start dates and end dates that don't necessarily correspond with each other */ /* This makes sure we only choose end dates that fall after the start date, and choosing the MIN means for the particular start_date, we get the closest end date that does not coincide with the start of another task */ where start_date < end_date GROUP BY start_date ORDER BY datediff(start_date, MIN(end_date)) DESC, start_date
+ 10 comments For MS SQL users:
SELECT T1.Start_Date,T2.End_Date FROM ( SELECT Start_Date,ROW_NUMBER() OVER (ORDER BY Start_Date) RN FROM Projects WHERE Start_Date NOT IN (SELECT END_Date FROM Projects) ) AS T1 INNER JOIN (
SELECT End_Date,ROW_NUMBER() OVER (ORDER BY End_Date) RN FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)
) AS T2 ON T1.RN = T2.RN ORDER BY DATEDIFF(Day,T1.Start_Date,T2.End_Date),T1.Start_Date
For everyone:
The way to solve this problem is to find Start_date that is not in End_Date and End_Date that is not in Start_Date.
Why? because if a Start_date is not in EndDate, it means it is a new project. And if a End_Date is not in Start_Date, it means this End_Date is the End_Date of a project.
We Gerenate a row number and Order by start_date so that it can match the Project's EndDate.
Same logic, We Gerenate a row number and Order by End_date so that it can match the Project's StartDate.
If a rownumber from Start_Date match the rownumber from End_Date, they must be the start_Date and End_Date in the same project.
think about it.
+ 11 comments This is more simpler solution using "TABIBITOSAN METHOD"
select sd,ed from ( select MIN(start_date) as sd,MAX(end_date) as ed,(MAX(end_date)- MIN(start_date)) as day_diff from ( select end_date-1 as start_date ,end_date as end_date ,row_number() over (order by end_date) as rn ,end_date-row_number() over (order by end_date) as end_date_grp from projects ) GROUP BY end_date_grp ORDER BY 3,1 );
You can refer the link to understand what is tabibitosan method, https://community.oracle.com/docs/DOC-915680
Thanks, Madhan
+ 19 comments My simple solution in SQL Server:
;WITH CTE_Projects AS ( SELECT Start_Date, End_Date, ROW_NUMBER() OVER (ORDER BY Start_Date) AS RowNumber FROM Projects WITH (NOLOCK) ) SELECT MIN(Start_Date), MAX(End_Date) FROM CTE_Projects WITH (NOLOCK) GROUP BY DATEDIFF(DAY, RowNumber, Start_Date) ORDER BY DATEDIFF(DAY, MIN(Start_Date), MAX(End_Date)), MIN(Start_Date)
The solution query can be acheived very easily without using any JOIN so I wonder why the Creator of this Challenge had placed this under the "Advanced Join" section
Sort 752 Discussions, By:
Please Login in order to post a comment