Hacker Rank SQL

SQL Project Planning Solution

Hello Friends in this article i am gone to share Hacker Rank SQL Solutions with you | SQL Project Planning Solution


Also Visit:ย  Contest Leaderboard Solution


 

Problem

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.

Column Type
Task_ID Integer
Start_Date Date
End_Date Date

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.

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.

Sample Input

Task_ID Start_Date End_Date
1 2015-10-01 2015-10-02
2 2015-10-02 2015-10-03
3 2015-10-03 2015-10-04
4 2015-10-13 2015-10-14
5 2015-10-14 2015-10-15
6 2015-10-28 2015-10-29
7 2015-10-30 2015-10-31

Sample Output

2015-10-28 2015-10-29
2015-10-30 2015-10-31
2015-10-13 2015-10-15
2015-10-01 2015-10-04

Explanation

The example describes followingย fourย projects:

  • Project 1: Tasksย 1,ย 2ย andย 3ย are completed on consecutive days, so these are part of the project. Thus start date of project isย 2015-10-01ย and end date isย 2015-10-04, so it tookย 3 daysย to complete the project.
  • Project 2: Tasksย 4ย andย 5ย are completed on consecutive days, so these are part of the project. Thus, the start date of project isย 2015-10-13ย and end date isย 2015-10-15, so it tookย 2 daysย to complete the project.
  • Project 3: Only taskย 6ย is part of the project. Thus, the start date of project isย 2015-10-28ย and end date isย 2015-10-29, so it tookย 1 dayย to complete the project.
  • Project 4: Only taskย 7ย is part of the project. Thus, the start date of project isย 2015-10-30ย and end date isย 2015-10-31, so it tookย 1 dayย to complete the project.

 

Solution โ€“ SQL Project Planning

MySQL Code
SELECTย START_DATE,ย MIN(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ย (MIN(END_DATE)ย -ย START_DATE),ย START_DATE;

 

 

Disclaimer: The above Problems are generated by Hacker Rank but the Solutions are Provided by NYANDER.COM. All Hacker Rank SQL Solutions Shared only for Educational and Learning Purpose.