We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  • Hackerrank Home
  • Prepare
    NEW
  • Certify
  • Compete
  • Career Fair
  • Hiring developers?
  1. Prepare
  2. SQL
  3. Advanced Join
  4. SQL Project Planning
  5. Discussions

SQL Project Planning

Problem
Submissions
Leaderboard
Discussions

Sort 752 Discussions, By:

votes

Please Login in order to post a comment

  • timcottenio
    6 years ago+ 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.

    207|
    Permalink
    View more Comments..
  • hellosolvur
    5 years ago+ 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
    
    105|
    Permalink
    View more Comments..
  • TingChiehHuang
    6 years ago+ 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.

    74|
    Permalink
    View more Comments..
  • madhankumaroffer
    6 years ago+ 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

    41|
    Permalink
    View more Comments..
  • san28v
    5 years ago+ 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

    31|
    Permalink
    View more Comments..
Load more conversations

Need Help?


View top submissions
  • Blog
  • Scoring
  • Environment
  • FAQ
  • About Us
  • Support
  • Careers
  • Terms Of Service
  • Privacy Policy
  • Request a Feature