해커랭크 SQL Project Planning (연속 날짜, GAP 분석)

https://www.hackerrank.com/challenges/sql-projects/problem

SQL Project Planning | HackerRank

---

문제 정의 (영어 -> 한국어 번역 후 문제 파악하기)

Task_ID , Start_Date , End_Date 라는 세 개의 열이 있는 Projects 테이블이 주어졌습니다 . 테이블의 각 행에서 End_Date 와 Start_Date 의 차이는 1 일 입니다 .

 

작업의 종료일이 연속적 이면 해당 작업들은 동일한 프로젝트에 속합니다. Samantha는 완료된 여러 프로젝트의 총 개수를 알고 싶어합니다. 프로젝트 완료에 걸린 일수를 기준으로 프로젝트의 시작일과 종료일을 오름차순으로 출력하는 쿼리를 작성하세요. 완료일이 같은 프로젝트가 여러 개 있는 경우, 프로젝트 시작일을 기준으로 정렬하세요.

 

문제 접근

• 프로젝트 기간별로 시작일과 종료일을 오름차순으로 출력
  • 시작일자를 기준으로 rnk 생성
  • 시작일자와 rnk간 gap을 구해서 그룹키 구하기
    -> 연속적인 일자면 rnk를 빼면 동일한 값으로 그룹키 생성
  • 그룹키별로 프로젝트 시작일자, 종료일자 구하기
  • 정렬 : 프로젝트 기간별  오름차순, 프로젝트 시작일 오름차순

 

 

/*
-- 프로젝트 기간별로 시작일과 종료일을 오름차순으로 출력
-- 조건
	-- 시작일자를 기준으로 rnk 생성
	-- 시작일자와 rnk간 gap을 구해서 그룹키 구하기
	--	-> 연속적인 일자면 rnk를 빼면 동일한 값으로 그룹키 생성
	--	그룹키별로 프로젝트 시작일자, 종료일자 구하기
	--	정렬 : 프로젝트 기간별  오름차순, 프로젝트 시작일 오름차순
*/
-- 시작일자를 기준으로 rnk 생성
with start_date_ny as (
    select task_id
        , start_date
        , end_date
        , dense_rank() over(order by start_date asc) start_date_rn
    from projects
)
-- 시작일자와 rnk간 gap을 구해서 그룹키 구하기
, project_gr_key as (
    select task_id
        , start_date
        , end_date
        , start_date_rn
        , date_sub(start_date, interval start_date_rn day) gr_key
    from start_date_ny
)
--그룹키별로 프로젝트 시작일자, 종료일자 구하기
select min(start_date)
    , max(end_date)
from project_gr_key
group by gr_key
order by datediff(max(end_date), min(start_date)) asc, min(start_date) asc
;


-- 최적화 해보기
select min(start_date) start_date
	, max(end_date) end_date 
from (
    select task_id
        , start_date
        , end_date
        , dense_rank() over(order by start_date asc) start_date_rn
        , date_sub(start_date, 
                    interval dense_rank() over(order by start_date asc) day) gr_key
    from projects
) g
group by g.gr_key
order by datediff(max(end_date), min(start_date)) asc
	, min(start_date) asc
;

---

-- LAG: 이전 행 데이터 가져오기
LAG(column_name, offset, default_value) OVER (ORDER BY ...)

-- LEAD: 다음 행 데이터 가져오기  
LEAD(column_name, offset, default_value) OVER (ORDER BY ...)

 

DENSE_RANK 특징:

• 동점자가 있어도 순위에 빈 구멍이 없어
• 모든 데이터 타입(숫자, 문자, 날짜)에 다 쓸 수 있어

예시로 비교:

 

sql

-- 점수: 100, 95, 95, 90, 85
ROW_NUMBER():   1, 2, 3, 4, 5    -- 무조건 연속
RANK():         1, 2, 2, 4, 5    -- 동점 후 건너뜀  
DENSE_RANK():   1, 2, 2, 3, 4    -- 동점 후 연속

연속 날짜 문제에서 DENSE_RANK 쓰는 이유:

• 날짜가 중복될 수 있어 (같은 시작일)
• 빈 구멍 없는 연속 번호가 필요해서!

 

sql

-- 만약 Start_Date가 중복된다면?
-- 2015-10-01, 2015-10-01, 2015-10-02
RANK():       1, 1, 3  ← 2번이 없어서 계산 틀림!
DENSE_RANK(): 1, 1, 2  ← 연속돼서 계산 정확!

결론: DENSE_RANK는 **"연속성이 중요한 모든 상황"**에서 사용💪