Prepare > SQL > Advanced Join > SQL Project Planning
2023. 6. 6. 14:37ㆍHackerRank-My SQL
SQL Project Planning | HackerRank
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.
www.hackerrank.com
문제
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.
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.
=> 개별 프로젝트의 시작일, 종료일을 걸린 기간, 시작일 기준으로 정렬해 쿼리하라.
PROJECTS는 죄다 하루짜리 프로젝트로 나온다.
그것들을 이어진 개별 프로젝트별로 묶으란 지시는 프로젝트가 하나씩만 진행 됐다는 점을 암묵적으로 알려준다.
코드
SELECT
Start_Date,
MIN(End_Date)
FROM
(SELECT Start_Date FROM PROJECTS
WHERE Start_Date NOT IN (SELECT End_Date FROM PROJECTS)) AS A,
(SELECT End_Date FROM PROJECTS
WHERE End_Date NOT IN (SELECT Start_Date FROM PROJECTS)) AS B
WHERE Start_Date < End_Date
GROUP BY Start_Date
ORDER BY DATEDIFF(MIN(End_Date), Start_Date), Start_Date
노트
Start_Date가 End_Date에 없으면 한 프로젝트의 시작일
End_Date가 Start_Date에 없으면 한 프로젝트의 종료일
개별적인 프로젝트들의 시작 날짜
SELECT Start_Date FROM PROJECTS
WHERE Start_Date NOT IN (SELECT End_Date FROM PROJECTS)더보기
2015-10-01
2015-10-11
2015-10-15
2015-10-17
2015-10-19
2015-10-21
2015-10-25
2015-11-01
2015-11-04
2015-11-11
2015-11-17- 하아! 총 11개의 프로젝트가 시행되었구나
각 프로젝트들의 종료 날짜
SELECT End_Date FROM PROJECTS
WHERE End_Date NOT IN (SELECT Start_Date FROM PROJECTS)더보기
2015-10-05
2015-10-13
2015-10-16
2015-10-18
2015-10-20
2015-10-22
2015-10-31
2015-11-02
2015-11-08
2015-11-13
2015-11-18
두 select문을 합쳐보자
SELECT
Start_Date,
End_Date
FROM
(SELECT Start_Date FROM PROJECTS
WHERE Start_Date NOT IN (SELECT End_Date FROM PROJECTS)) AS A,
(SELECT End_Date FROM PROJECTS
WHERE End_Date NOT IN (SELECT Start_Date FROM PROJECTS)) AS B더보기
2015-11-17 2015-10-05 # 121행
2015-11-11 2015-10-05
2015-11-04 2015-10-05
2015-11-01 2015-10-05
2015-10-25 2015-10-05
2015-10-21 2015-10-05
2015-10-19 2015-10-05
2015-10-17 2015-10-05
2015-10-15 2015-10-05
2015-10-11 2015-10-05
2015-10-01 2015-10-05
2015-11-17 2015-10-13
2015-11-11 2015-10-13
2015-11-04 2015-10-13
2015-11-01 2015-10-13
2015-10-25 2015-10-13
2015-10-21 2015-10-13
2015-10-19 2015-10-13
2015-10-17 2015-10-13
2015-10-15 2015-10-13
2015-10-11 2015-10-13
2015-10-01 2015-10-13
2015-11-17 2015-10-16
2015-11-11 2015-10-16
2015-11-04 2015-10-16
2015-11-01 2015-10-16
2015-10-25 2015-10-16
2015-10-21 2015-10-16
2015-10-19 2015-10-16
2015-10-17 2015-10-16
2015-10-15 2015-10-16
2015-10-11 2015-10-16
2015-10-01 2015-10-16
2015-11-17 2015-10-18
2015-11-11 2015-10-18
2015-11-04 2015-10-18
2015-11-01 2015-10-18
2015-10-25 2015-10-18
2015-10-21 2015-10-18
2015-10-19 2015-10-18
2015-10-17 2015-10-18
2015-10-15 2015-10-18
2015-10-11 2015-10-18
2015-10-01 2015-10-18
2015-11-17 2015-10-20
2015-11-11 2015-10-20
2015-11-04 2015-10-20
2015-11-01 2015-10-20
2015-10-25 2015-10-20
2015-10-21 2015-10-20
2015-10-19 2015-10-20
2015-10-17 2015-10-20
2015-10-15 2015-10-20
2015-10-11 2015-10-20
2015-10-01 2015-10-20
2015-11-17 2015-10-22
2015-11-11 2015-10-22
2015-11-04 2015-10-22
2015-11-01 2015-10-22
2015-10-25 2015-10-22
2015-10-21 2015-10-22
2015-10-19 2015-10-22
2015-10-17 2015-10-22
2015-10-15 2015-10-22
2015-10-11 2015-10-22
2015-10-01 2015-10-22
2015-11-17 2015-10-31
2015-11-11 2015-10-31
2015-11-04 2015-10-31
2015-11-01 2015-10-31
2015-10-25 2015-10-31
2015-10-21 2015-10-31
2015-10-19 2015-10-31
2015-10-17 2015-10-31
2015-10-15 2015-10-31
2015-10-11 2015-10-31
2015-10-01 2015-10-31
2015-11-17 2015-11-02
2015-11-11 2015-11-02
2015-11-04 2015-11-02
2015-11-01 2015-11-02
2015-10-25 2015-11-02
2015-10-21 2015-11-02
2015-10-19 2015-11-02
2015-10-17 2015-11-02
2015-10-15 2015-11-02
2015-10-11 2015-11-02
2015-10-01 2015-11-02
2015-11-17 2015-11-08
2015-11-11 2015-11-08
2015-11-04 2015-11-08
2015-11-01 2015-11-08
2015-10-25 2015-11-08
2015-10-21 2015-11-08
2015-10-19 2015-11-08
2015-10-17 2015-11-08
2015-10-15 2015-11-08
2015-10-11 2015-11-08
2015-10-01 2015-11-08
2015-11-17 2015-11-13
2015-11-11 2015-11-13
2015-11-04 2015-11-13
2015-11-01 2015-11-13
2015-10-25 2015-11-13
2015-10-21 2015-11-13
2015-10-19 2015-11-13
2015-10-17 2015-11-13
2015-10-15 2015-11-13
2015-10-11 2015-11-13
2015-10-01 2015-11-13
2015-11-17 2015-11-18
2015-11-11 2015-11-18
2015-11-04 2015-11-18
2015-11-01 2015-11-18
2015-10-25 2015-11-18
2015-10-21 2015-11-18
2015-10-19 2015-11-18
2015-10-17 2015-11-18
2015-10-15 2015-11-18
2015-10-11 2015-11-18
2015-10-01 2015-11-18- FROM 에 2개의 서브쿼리를 쓰면 그냥 cross join인가...?
- 근데 Start_Date랑 End_Date랑 섞이지는 않음.
- 솔직히 이해 안됨
시작일 < 종료일
SELECT
Start_Date,
End_Date
FROM
(SELECT Start_Date FROM PROJECTS
WHERE Start_Date NOT IN (SELECT End_Date FROM PROJECTS)) AS A,
(SELECT End_Date FROM PROJECTS
WHERE End_Date NOT IN (SELECT Start_Date FROM PROJECTS)) AS B
WHERE Start_Date < End_Date더보기
2015-10-01 2015-10-05 # 66행
2015-10-11 2015-10-13
2015-10-01 2015-10-13
2015-10-15 2015-10-16
2015-10-11 2015-10-16
2015-10-01 2015-10-16
2015-10-17 2015-10-18
2015-10-15 2015-10-18
2015-10-11 2015-10-18
2015-10-01 2015-10-18
2015-10-19 2015-10-20
2015-10-17 2015-10-20
2015-10-15 2015-10-20
2015-10-11 2015-10-20
2015-10-01 2015-10-20
2015-10-21 2015-10-22
2015-10-19 2015-10-22
2015-10-17 2015-10-22
2015-10-15 2015-10-22
2015-10-11 2015-10-22
2015-10-01 2015-10-22
2015-10-25 2015-10-31
2015-10-21 2015-10-31
2015-10-19 2015-10-31
2015-10-17 2015-10-31
2015-10-15 2015-10-31
2015-10-11 2015-10-31
2015-10-01 2015-10-31
2015-11-01 2015-11-02
2015-10-25 2015-11-02
2015-10-21 2015-11-02
2015-10-19 2015-11-02
2015-10-17 2015-11-02
2015-10-15 2015-11-02
2015-10-11 2015-11-02
2015-10-01 2015-11-02
2015-11-04 2015-11-08
2015-11-01 2015-11-08
2015-10-25 2015-11-08
2015-10-21 2015-11-08
2015-10-19 2015-11-08
2015-10-17 2015-11-08
2015-10-15 2015-11-08
2015-10-11 2015-11-08
2015-10-01 2015-11-08
2015-11-11 2015-11-13
2015-11-04 2015-11-13
2015-11-01 2015-11-13
2015-10-25 2015-11-13
2015-10-21 2015-11-13
2015-10-19 2015-11-13
2015-10-17 2015-11-13
2015-10-15 2015-11-13
2015-10-11 2015-11-13
2015-10-01 2015-11-13
2015-11-17 2015-11-18
2015-11-11 2015-11-18
2015-11-04 2015-11-18
2015-11-01 2015-11-18
2015-10-25 2015-11-18
2015-10-21 2015-11-18
2015-10-19 2015-11-18
2015-10-17 2015-11-18
2015-10-15 2015-11-18
2015-10-11 2015-11-18
2015-10-01 2015-11-18
Start_Date 중복
SELECT
Start_Date,
MIN(End_Date)
FROM
(SELECT Start_Date FROM PROJECTS
WHERE Start_Date NOT IN (SELECT End_Date FROM PROJECTS)) AS A,
(SELECT End_Date FROM PROJECTS
WHERE End_Date NOT IN (SELECT Start_Date FROM PROJECTS)) AS B
WHERE Start_Date < End_Date
GROUP BY Start_Date더보기
2015-10-01 2015-10-05
2015-10-11 2015-10-13
2015-10-15 2015-10-16
2015-10-17 2015-10-18
2015-10-19 2015-10-20
2015-10-21 2015-10-22
2015-10-25 2015-10-31
2015-11-01 2015-11-02
2015-11-04 2015-11-08
2015-11-11 2015-11-13
2015-11-17 2015-11-18- GROUP BY 와 MIN(End_Date)로 해결
더보기
INPUT
SELECT *
FROM (
SELECT Start_Date FROM PROJECTS WHERE Start_Date NOT IN (SELECT End_Date FROM PROJECTS)) AS K1
LEFT OUTER JOIN (
SELECT End_Date FROM PROJECTS WHERE End_Date NOT IN (SELECT Start_Date FROM PROJECTS)) AS K2
ON Start_Date < End_Date
ORDER BY Start_Date, End_Date
OUTPUT
2015-10-01 2015-10-05 # 2015-10-01 ~, 11행
2015-10-01 2015-10-13
2015-10-01 2015-10-16
2015-10-01 2015-10-18
2015-10-01 2015-10-20
2015-10-01 2015-10-22
2015-10-01 2015-10-31
2015-10-01 2015-11-02
2015-10-01 2015-11-08
2015-10-01 2015-11-13
2015-10-01 2015-11-18
2015-10-11 2015-10-13 # 2015-10-11 ~, 10행
2015-10-11 2015-10-16
2015-10-11 2015-10-18
2015-10-11 2015-10-20
2015-10-11 2015-10-22
2015-10-11 2015-10-31
2015-10-11 2015-11-02
2015-10-11 2015-11-08
2015-10-11 2015-11-13
2015-10-11 2015-11-18
2015-10-15 2015-10-16 # 2015-10-15 ~, 9행
2015-10-15 2015-10-18
2015-10-15 2015-10-20
2015-10-15 2015-10-22
2015-10-15 2015-10-31
2015-10-15 2015-11-02
2015-10-15 2015-11-08
2015-10-15 2015-11-13
2015-10-15 2015-11-18
2015-10-17 2015-10-18 # 2015-10-17 ~, 8행
2015-10-17 2015-10-20
2015-10-17 2015-10-22
2015-10-17 2015-10-31
2015-10-17 2015-11-02
2015-10-17 2015-11-08
2015-10-17 2015-11-13
2015-10-17 2015-11-18
2015-10-19 2015-10-20 # 2015-10-19 ~, 7행
2015-10-19 2015-10-22
2015-10-19 2015-10-31
2015-10-19 2015-11-02
2015-10-19 2015-11-08
2015-10-19 2015-11-13
2015-10-19 2015-11-18
2015-10-21 2015-10-22 # # 2015-10-21 ~, 6행
2015-10-21 2015-10-31
2015-10-21 2015-11-02
2015-10-21 2015-11-08
2015-10-21 2015-11-13
2015-10-21 2015-11-18
2015-10-25 2015-10-31 # 2015-10-25 ~, 5행
2015-10-25 2015-11-02
2015-10-25 2015-11-08
2015-10-25 2015-11-13
2015-10-25 2015-11-18
2015-11-01 2015-11-02 # 2015-11-01 ~, 4행
2015-11-01 2015-11-08
2015-11-01 2015-11-13
2015-11-01 2015-11-18
2015-11-04 2015-11-08 # 2015-11-04 ~, 3행
2015-11-04 2015-11-13
2015-11-04 2015-11-18
2015-11-11 2015-11-13 # 2015-11-11 ~, 2행
2015-11-11 2015-11-18
2015-11-17 2015-11-18 # # 2015-11-17 ~, 1행LEFT OUTER JOIN의 결과를 보면 Start_Date 와 End_Date가 CROSS JOIN 된 것을 알 수 있음
당연하게도 제일 Start_Date에 제일 가까운 End_Date만 프로젝트 종료일로서 의미가 있음
나머지는 다른 프로젝트들의 종료일
정렬
SELECT
Start_Date,
MIN(End_Date)
FROM
(SELECT Start_Date FROM PROJECTS
WHERE Start_Date NOT IN (SELECT End_Date FROM PROJECTS)) AS A,
(SELECT End_Date FROM PROJECTS
WHERE End_Date NOT IN (SELECT Start_Date FROM PROJECTS)) AS B
WHERE Start_Date < End_Date
GROUP BY Start_Date
ORDER BY DATEDIFF(MIN(End_Date), Start_Date), Start_Date더보기
2015-10-15 2015-10-16
2015-10-17 2015-10-18
2015-10-19 2015-10-20
2015-10-21 2015-10-22
2015-11-01 2015-11-02
2015-11-17 2015-11-18
2015-10-11 2015-10-13
2015-11-11 2015-11-13
2015-10-01 2015-10-05
2015-11-04 2015-11-08
2015-10-25 2015-10-31
두 번째 시도
SELECT Start_Date, MIN(End_Date)
FROM (
SELECT Start_Date FROM PROJECTS WHERE Start_Date NOT IN (SELECT End_Date FROM PROJECTS)) AS K1
LEFT OUTER JOIN (
SELECT End_Date FROM PROJECTS WHERE End_Date NOT IN (SELECT Start_Date FROM PROJECTS)) AS K2
ON Start_Date < End_Date
GROUP BY Start_Date
ORDER BY MIN(End_Date) - Start_Date ASC, Start_Date- LEFT OUTER JOIN이나 CROSS JOIN이나....여기선 같지
- JOIN 조건(ON)으로 시간선 맞춰주고
- GROUP BY로 잡아준건...위랑 같네
'HackerRank-My SQL' 카테고리의 다른 글
| Prepare > SQL > Advanced Join > Symmetric Pairs (0) | 2023.06.06 |
|---|---|
| Prepare > SQL > Advanced Join > Placements (0) | 2023.06.06 |
| Prepare > SQL > Basic Join > Contest Leaderboard (0) | 2023.05.31 |
| Prepare > SQL > Basic Join > Challenges (0) | 2023.05.30 |
| Prepare > SQL > Basic Join > Ollivander's Inventory (0) | 2023.05.28 |