SQL 코드카타_01

SQL 코드카타_SQL Project Planning

SQL Project Planning

문제 : SQL Project Planning

# 목적, 목표 : 프로젝트 완료 기간 기준 오름차순으로 시작일과 종료일 출력
# 필요한 컬럼 : Start_Date, End_Date
# 컬럼 조건 : 이전 행과 비교 (LAG)
# 사용할 테이블 : Projects
# join key : 없음
# 테이블 조건 : 시작일 = 이전 행의 종료일 → 동일한 프로젝트, 시작일 != 이전 행의 종료일 → 개별 프로젝트
# 필요한 그룹 : 없음
# 필요한 그룹 조건 : 없음
# 정렬 기준 : 프로젝트 완료 기간 기준 오름차순, 시작일을 기준 오름차순
# 주의사항 : 동일한 프로젝트라면, 시작일 = 첫 작업의 시작일, 종료일 = 끝 작업의 종료일이어야 함

로직

• Start_Date와 이전 행의 End_Date를 비교
  • 이전 행의 End_Date를 LAG_End로 칭함
• 개별 프로젝트만 추출
  • Start_Date != LAG_End 이거나 LAG_End가 null인 경우
  • 동일한 프로젝트의 시작일과 종료일 사이의 기간을 제거하기 위함
• 동일 프로젝트 종료일 끌어오기
  • LEAD(LAG_End)로 끌어오기
  • LEAD(LAG_End)를 LEAD_End로 칭함
• 동일 프로젝트 종료일 알맞게 배치하기
  • LEAD_End가 null이면 End_Date를 끌어오고 다른 경우는 LEAD_End를 끌어오기
• 프로젝트 기간 산정하기
  • DATEDIFF(End_Date, Start_Date)+1, Days로 칭함
• 프로젝트 기간 및 시작일 기준으로 정렬하기
  • ORDER BY Days, Start_Date

WITH A as(
SELECT Start_Date, LAG(End_Date) OVER (ORDER BY Start_Date) AS LAG_End, End_Date
FROM Projects),
B as (
SELECT Start_Date, LAG_End, End_Date
FROM A
WHERE Start_Date != LAG_End OR LAG_End is null),
C as (
SELECT Start_Date, LAG_End, End_Date, LEAD(LAG_End) OVER (ORDER BY Start_Date) AS LEAD_End
FROM B),
D as (
SELECT Start_Date, 
    CASE WHEN LEAD_End is null THEN End_Date
        ELSE LEAD_End END AS End_Date
FROM C),
E as (
SELECT Start_Date, End_Date, DATEDIFF(End_Date, Start_Date)+1 AS Days
FROM D)
SELECT Start_Date, End_Date
FROM E
ORDER BY Days, Start_Date

[ 쿼리 뜯어보기 ]

# A 테이블 : Start_Date와 이전 행의 End_Date를 비교하기 위해 LAG_End 컬럼 생성
WITH A as(
SELECT Start_Date, LAG(End_Date) OVER (ORDER BY Start_Date) AS LAG_End, End_Date
FROM basic.Projects),

# B 테이블 : 개별 프로젝트만 추출
B as (
SELECT Start_Date, LAG_End, End_Date
FROM A
WHERE Start_Date != LAG_End OR LAG_End is null),

# C 테이블 : 동일 프로젝트 종료일을 알맞게 배치하기 위해 LEAD_End 컬럼 생성
C as (
SELECT Start_Date, LAG_End, End_Date, LEAD(LAG_End) OVER (ORDER BY Start_Date) AS LEAD_End
FROM B),

# D 테이블 : 동일 프로젝트 종료일을 알맞게 배치하기
D as (
SELECT Start_Date, 
	CASE WHEN LEAD_End is null THEN End_Date
		ELSE LEAD_End END AS End_Date
FROM C),

# E 테이블 : 프로젝트 기간 산정하기
E as (
SELECT Start_Date, End_Date, DATEDIFF(End_Date, Start_Date)+1 AS Days
FROM D)

# 프로젝트 기간, 시작일 기준 오름차순 정렬하기
SELECT Start_Date, End_Date
FROM E
ORDER BY Days, Start_Date

[ 어려웠던 점 ]

• 로직을 구성하는 데에 어려움이 있었음
• 노트에 데이터를 하나하나 적고 머리를 굴리며 한참을 생각했다...

[ 새로 알게된 점 ]

• 서로 다른 행의 종료일과 시작일이 같을 때, 이를 연속된 기간으로 두고 시작일과 종료일을 맞추는 방법을 알게됨
• 이전 종료일을 끌어와서 시작일과 같은 경우 제거하고 남은 데이터들의 이전 종료일을 다시 종료일로 끌어오면 짜잔 ! 되더라
• 노가다로 알아내느라 힘들었지만.. 그래도 뿌듯

---

SQL 코드카타_SQL Placements

Placements

문제 : Placements

# 목적, 목표 : 가장 친한 친구가 자신보다 더 높은 급여를 제안받은 학생들의 이름을 출력
# 필요한 컬럼 : ID, Friend_ID, Salary, Name
# 컬럼 조건 :
# 사용할 테이블 : Packages, Friends, Salary
# join key : ID, Friend_ID
# 테이블 조건 : 가장 친한 친구가 자신보다 더 높은 급여
# 필요한 그룹 : 없음
# 필요한 그룹 조건 : 없음
# 정렬 기준 : 가장 친한 친구에게 제안된 급여를 기준으로 정렬

로직

• ID, Friend_ID 기준으로 Packages와 Friends JOIN
• 가장 친한 친구가 자신보다 더 높은 급여를 제안받은 ID만 추출
  • Friend_ID 기준 Salary > ID 기준 Salary만 추출
• ID 기준으로 Name JOIN하여 Name 추출
• 급여 기준으로 오름차순 정렬
  • ORDER BY a.Salary

WITH A AS (
SELECT f1.ID, p2.Salary
FROM Packages p1 inner join Friends f1 on p1.ID = f1.ID inner join Packages p2 on f1.Friend_ID = p2.ID
WHERE p2.Salary > p1.Salary)

SELECT s.Name
FROM A a inner join Students s on a.ID = s.ID
ORDER BY a.Salary

[ 쿼리 뜯어보기 ]

# A 테이블 : 가장 친한 친구가 자신보다 더 높은 급여를 제안받은 ID만 추출
WITH A AS (
SELECT f1.ID, p2.Salary
FROM Packages p1 inner join Friends f1 on p1.ID = f1.ID inner join Packages p2 on f1.Friend_ID = p2.ID
WHERE p2.Salary > p1.Salary)

# Name 추출 및 급여 오름차순으로 정렬
SELECT s.Name
FROM A a inner join Students s on a.ID = s.ID
ORDER BY a.Salary

[ 어려웠던 점 ]

• 로직을 구성하는 데에 어려움이 있었음
• FROM Friends f1 inner join Packages p1 on f1.ID = p1.ID inner join Friends f2 on p1.ID = f2.Friend_ID로 함
  • JOIN한 Packages 테이블은 1개라서 WHERE 절에서 ID와 Friend_ID별 Salary 비교 불가

[ 새로 알게된 점 ]

• Packages 테이블을 별칭을 달리하여 2번 JOIN시킴으로써 WHERE절에서 p2.Salary > p1.Salary로 조건 비교 가능해짐