QCC 5회차 오답

주말에 다시 한번 복습하며 작성하는 QCC 오답 🌧️

---

문제1 (X)

2012년 이후 개봉한 영화 중, 장르가 2개 이상 포함된 영화의 연도별 매출을 구하는 SQL 문을 작성해주세요. 단, 해당 영화는 최소 100개 이상의 평점 수를 받은 경우만 결과에 포함해주세요. 결과는 개봉 연도를 기준으로 오름차 정렬해야 합니다.

SELECT release_year, 
	SUM(revenue) revenue -- 연도별 매출이므로 SUM
FROM movies
WHERE release_year >= 2012
AND JSON_LENGTH(genres) >= 2
AND vote_count >= 100 
GROUP BY release_year
ORDER BY release_year ;

• JSON_LENGTH(*) 함수: JSON 객체나 배열의 길이 계산
• SUM!! 놓치지 말고 잘 챙기기
• GROUP BY movie_id 가 아니라 release_year인 이유가 잘 이해되지 않음..!
• '개봉연도가 같은 영화의 연도별 매출 합을 구하시오. 조건: 개봉 연도는 2012년 이후, 장르는 2개 이상, 최소 100개 이상의 평점'이라고 더 명확하게 표현했으면 좋았을듯

---

문제2 (O)

각 고객의 총 결제 금액(total_due)을 계산하고, 결제 금액을 기준으로 순위를 매기세요. 결제 금액이 같은 고객은 같은 순위를 가져야 합니다. 결과는 총 결제 금액이 높은 순으로 내림차 정렬하되, 동일한 금액일 경우 고객 ID 기준으로 오름차 정렬하여 상위 5순위의 고객 정보를 반환해야 합니다.

-- 방식1 (내 풀이)
SELECT customer_id, 
	SUM(total_due) total_order_amount,
	DENSE_RANK() OVER(ORDER BY SUM(total_due) DESC) rn
FROM sales_order_header
GROUP BY customer_id 
ORDER BY total_order_amount DESC,
	customer_id
LIMIT 5;

-- 방식2 (튜터님 풀이)
WITH customer_totals AS (
	SELECT customer_id, 
		SUM(total_due) total_order_amount
	FROM sales_order_header
	GROUP BY customer_id 
	),
	customer_ranks AS (
	SELECT customer_id,
		total_order_amount, 
		DENSE_RANK() OVER (ORDER BY total_order_amount DESC) AS rn
	FROM customer_totals
	)
SELECT *
FROM customer_ranks
WHERE rn <= 5
ORDER BY total_order_amount DESC, 
	customer_id;

• 결제금액이 같은 고객이 같은 순위를 가지고, 그 뒤 고객이 바로 다음 순위를 가지려면 DENSE_RANK를 써야함
  • 결과값 예시 외에 문제에도 명시되어 있다면 좋았을듯
• 내 풀이방식의 단점
  • 중복 계산이 발생 가능
    • SUM(total_due)를 두 번 사용(한 번은 SELECT, 한 번은 ORDER BY) → 불필요한 중복 연산 발생 가능
    • 만약 total_due의 데이터가 매우 많다면 성능 저하가 발생할 수 있음
  • 가독성이 떨어질 수 있음
    • 랭킹 계산과 정렬을 한 번에 처리하므로 복잡한 쿼리가 추가되면 유지보수가 어려울 가능성이 있음

---

문제3 (X)

Slowly Changing Dimension(SCD) Type 2 형식의 데이터 테이블을 생성하는 SQL 문을 작성하세요.

[SCD Type 2란 무엇인가?] 
데이터 변경 이력을 추적하기 위해 기존 데이터를 수정하지 않고, 변경된 상태를 별도의 새로운 행으로 추가하는 방식입니다. 각 행에는 상태 시작 날짜, 종료 날짜, 그리고 현재 활성 상태를 나타내는 정보가 포함되며, 과거와 현재 상태를 명확히 구분할 수 있습니다.

• SCD Type 2 결과 형식

컬럼명	타입	설명
customer_id	INT	고객 ID
status	VARCHAR	멤버십 상태 (‘ACTIVE’, ‘INACTIVE’)
start_date	DATE	상태가 시작된 날짜
end_date	DATE	상태가 끝난 날짜 (NULL이면 활성)
current_flag	BOOLEAN	현재 ACTIVE 및 활성 상태 여부 (1 = 활성)

 

• 멤버십 상태 (status)
  • JOIN - 가입 이벤트는 고객의 멤버십 상태를 ACTIVE 상태로 변환합니다.
  • WITHDRAW - 해지 이벤트는 고객의 멤버십 상태를 INACTIVE 상태로 변환합니다.
• 기간 설정 (start_date, end_date)
  • start_date는 이벤트 발생 날짜(event_date)로 설정합니다.
  • end_date는 다음 이벤트의 event_date의 전날로 설정합니다.
  • 가장 최신 상태는 end_date가 NULL이어야 합니다.
• 활성 상태 플래그 (current_flag)
  • 현재 멤버십 상태가 ACTIVE 이면서 상태가 활성(end_date is NULL)인 경우 current_flag를 1로 설정합니다.
  • 비활성 상태는 current_flag를 0으로 설정합니다.
• 정렬
  • 결과는 customer_id와 start_date를 기준으로 오름차순 정렬합니다.

WITH ranked_events AS (
	SELECT customer_id,
		CASE WHEN event_type = 'JOIN' THEN 'ACTIVE'
			WHEN event_type = 'WITHDRAW' THEN 'INACTIVE'
		END AS status, 
		event_date AS start_date, 
		LEAD(event_date) OVER (PARTITION BY customer_id 
        				ORDER BY event_date) AS next_event_date
	FROM membership_history
	)
SELECT customer_id,
	status,
	start_date,
	DATE_SUB(next_event_date, INTERVAL 1 DAY) AS end_date,
	CASE WHEN status = 'ACTIVE' AND next_event_date IS NULL THEN 1
		ELSE 0
	END AS current_flag
FROM ranked_events
ORDER BY customer_id,
	start_date;

• LEAD() 윈도우 함수의 역할
  • LEAD(컬럼명, 이동할 행 수, 기본값) OVER (PARTITION BY 그룹컬럼 ORDER BY 정렬컬럼)
  • 현재 행을 기준으로 다음 행(미래 값)을 가져오는 함수 (LAG는 이전 행)
  • 같은 customer_id 그룹 내에서 event_date 기준 다음 이벤트 날짜를 가져옴
  • 즉, 특정 이벤트가 발생했을 때 다음 상태로 변경되는 날짜를 알아내는 것
  • PARTITION BY customer_id
    • 고객(customer_id) 별로 데이터를 그룹화하여 LEAD() 적용
    • 즉, 각 고객에 대해 개별적인 이벤트 타임라인 형성
  • ORDER BY event_date
    • event_date를 기준으로 정렬하여 다음 이벤트 날짜를 찾음
• DATE_SUB() 함수의 역할
  
  • DATE_SUB(날짜, INTERVAL X 단위)
  • 날짜에서 특정 기간(일, 월, 연도 등)을 빼는 함수
  • next_event_date에서 하루를 빼서 end_date 계산
  • 즉, JOIN 이벤트로 시작한 경우, 다음 WITHDRAW 이벤트의 하루 전까지 활동한 것으로 처리

👉 핵심 요약

• LEAD()로 다음 이벤트 날짜를 찾고
• DATE_SUB()로 활동 종료 날짜를 조정하며
• current_flag로 현재 활동 중인지 확인