코테준비 | 해커랭크 SQL Project Planning / Placements (연속된 그룹 필터링/다중 JOIN)

SQL Project Planning | HackerRank

 

Placements | HackerRank

 

☑️  SQL Project Planning

[문제]

• 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.
• 각각의 연속되지 않은 개별 프로젝트 조회 

---

[문제 풀이]

내 코드

WITH grouped_t AS (
SELECT Start_Date,End_Date
        ,end_date - ROW_NUMBER() OVER (ORDER BY End_Date) AS grouped
FROM Projects
GROUP BY Start_Date,End_Date -- non-duplicated
ORDER BY 1,2
)
SELECT Min(Start_Date)
      ,Max(End_Date)
FROM grouped_t
GROUP BY grouped
ORDER BY COUNT(grouped),Min(Start_Date)

 

이슈 및 해결 과정

• 문제 조건 
  • 출력 : 시작-끝 시각
  • 정렬 : 프로젝트 일수 오름차, 시작시각 오름차순 
• 아이디어 
  • 연속된 날짜별 그룹핑 해준 후 일수 카운팅 집계 및 최소 최대 집계 해줌 
• 풀이 
  • grouped_t CTE : 연속된 날짜 그룹핑 테이블 생성
    • group by는 혹시 모를 중복 대비 , order by 는 출력시 가독성을 위해 코드 추가 (생략 가능)
    • 연속 날짜 그룹핑(grouped 컬럼) : `끝시각(or 시작시각) - 순번 매기기`
  • 아우터 쿼리 : grouped 컬럼 기준 집계
    • 출력 값 : 연속 그룹별 최소 시작시간, 최대 끝 시간 집계
    • 정렬 조건 : 그룹별 일수 카운팅 집계 하여 오름차, 시작시간 오름차순 정렬 
• 연속된 날짜 관련 문제 복습 
  • 문제 ) 연속 5회 이상 베스트 셀러로 등재된 작가명/ 최신년도 /총 연속 횟수 조회하기
  • 1 단계 ) 작가별 연속 년도 그룹핑 (grouped 컬럼)
  • 2 단계 ) 5회 이상 (count(grouped)>=5) 필터링
  • 3단계 ) 작가명 / 최신 년도(MAX(year)) / 연속 횟수 ( Count(grouped)) 집계 

WITH consec AS(
  SELECT author,year
       ,year - ROW_NUMBER() OVER ( PARTITION BY author ORDER BY year) as grouped
  FROM books
  WHERE genre ='Fiction'
  GROUP BY author,year
)
SELECT author
      ,MAX(year) AS year
      ,COUNT(grouped) AS depth
FROM consec
GROUP BY author,grouped
HAVING COUNT(grouped)>=5

 

한줄 포인트

• 연속 날짜 문제는 그룹핑이 포인트 : `기존 시각 - 순번` 

☑️   Placements

[문제]

• Write a query to output the names of those students whose best friends got offered a higher salary than them. Names must be ordered by the salary amount offered to the best friends. It is guaranteed that no two students got same salary offer.
• 친구가 자신보다 더 높은 연봉을 받는 학생 이름 조회

---

[문제 풀이]

내 코드

SELECT s.name
FROM Students s
    INNER JOIN Friends f ON s.id = f.id -- non_null hypo
    INNER JOIN Packages p1 ON s.id = p1.id
    INNER JOIN packages p2 ON f.friend_id=p2.id
WHERE p1.salary < p2.salary
ORDER BY p2.salary

 

 

이슈 및 해결 과정

• 문제 조건
  
  • 조건 : 동일한 연봉은 없음 
  • 출력 : 학생명
  • 정렬: 친구 연봉 오름차순
• 아이디어 : 출력/정렬 조건에 테이블이 연결되어있으므로 3개 테이블 조인 한 후 필터링 진행
  • students - friends  테이블의 id 연결 (친구 id 연결)
  • students - packages 테이블의 id 연결 ( 학생 연봉 연결 )
  • friends - packages 테이블의 friend_id 연결 ( 학생의 친구 연봉 연결 )
  • 친구 연봉이 클때 (조건) 이름 조회 , 친구 연봉 오름차 정렬

한줄 포인트

• 다중 테이블이 주어졌을때, 전체 join이 필요한지 여부는 출력 형태를 기준으로 설정 할수 있음.