SQL Project Planning (HackerRank)

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. If there is more than one project that have the same number of completion days, then order by the start date of the project.

풀이

제출한 오답

with sub1 as (
    select start_Date, row_number() over (order by start_Date) rnk1
    from projects)
    
, sub2 as (
    select end_date, row_number() over (order by end_Date) rnk2
    from projects)

select start_Date, end_date
from sub1 join sub2 on sub1.rnk1 = sub2.rnk2

-> 모든 시작일과 종료일 출력됨

다시 제출한 답

with sub1 as (
    select start_Date, row_number() over (order by start_Date) rnk1
    from projects
    where start_Date not in (select end_Date from projects))
    
, sub2 as (
    select end_date, row_number() over (order by end_Date) rnk2
    from projects
    where end_Date not in (select start_Date from projects)
)

select sub1.start_Date, sub2.end_date
from sub1 join sub2 on sub1.rnk1 = sub2.rnk2
order by (sub2.end_date - sub1.start_Date), sub1.start_Date

-> 시작일과 종료일 완전 구분

2022.11.04