문제 1

movies 테이블은 영화에 대한 정보를 담고 있습니다. 테이블 구조는 다음과 같으며,
MOVIE_ID, GENRES, TITLE, POPULARITY, RELEASE_YEAR, REVENUE, VOTE_AVERAGE, VOTE_COUNT은 각각 영화 ID, 영화 장르 리스트, 영화 제목, 인기도, 개봉 연도, 매출, 평균 평점, 평점 수을 나타냅니다.

컬럼명	타입	설명
MOVIE_ID	INT	영화 ID (PK)
GENRE	JSON	영화 장르 리스트
TITLE	TEXT	영화 제목
POPULARITY	FLOAT	인기도
RELEASE_YEAR	BIGINT	개봉 연도
REVENUE	BIGINT	매출
VOTE_AVERAGE	FLOAT	평균 평점
VOTE_COUNT	INT	평점 수

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

풀이

SELECT release_year, SUM(revenue) AS renevue 
FROM movies
WHERE release_year >= 2012 AND JSON_LENGTH(genres) >= 2 AND vote_count >= 100
GROUP BY release_year 
ORDER BY release_year; 

📍 point

• JSON_LENGTH( ): JSON 객체, 배열의 길이를 계산해준다.

---

문제 2

sales_order_header 테이블은 판매 주문에 대한 정보를 담고 있습니다.
테이블 구조는 다음과 같으며, SalesOrderID, CustomerID, OrderDate, TotalDue는
각각 판매 주문 ID, 고객 ID, 주문 날짜, 총 결제 금액을 나타냅니다.

컬럼명	타입	설명
SalesOrderID	INT	판매 주문 ID (PK)
CustomerID	INT	고객 ID
OrderDate	DATETIME	주문 날짜
TotalDue	DOUBLE	총 결제 금액

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

풀이

WITH CTE AS (
	SELECT customer_id AS CustomerID, SUM(total_due) AS TotalOrderAmount,
    	DENSE_RANK () OVER (ORDER BY SUM(total_due) DESC) AS rn
	FROM sales_order_header
	GROUP BY customer_id
	ORDER BY rn, customer_id
) SELECT *
FROM CTE
WHERE rn <= 5;

📍 point

• DENSE_RANK window function을 이용하여 주어진 조건에 맞는 순위 구하기

---

문제 3

membership_history 테이블은 고객의 멤버십 가입 및 탈퇴 이벤트를 저장합니다.
고객이 멤버십에 가입하거나 탈퇴한 날짜가 기록됩니다.

컬럼명	타입	설명
customer_id	INT	고객 ID (PK)
event_type	VARCHAR	이벤트 유형 ('join', 'withdraw')
event_date	DATE	이벤트 발생 날짜

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	BOULEAN	현재 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를 기준으로 오름차순 정렬합니다.

풀이

CTE 쓰지 않는 버전

SELECT customer_id,
	IF(event_type = 'WITHDRAW','INACTIVE','ACTIVE') AS status,
	event_date AS start_date,
	DATE_SUB(LEAD (event_date) OVER (PARTITION BY customer_id ORDER BY event_date), INTERVAL 1 DAY) AS end_date,
	IF(IF(event_type = 'WITHDRAW','INACTIVE','ACTIVE') = 'ACTIVE' AND DATE_SUB(LEAD (event_date) OVER (PARTITION BY customer_id ORDER BY event_date), INTERVAL 1 DAY) IS NULL, 1, 0) AS current_flag
FROM membership_history mh 
ORDER BY customer_id, start_date;

CTE 쓰는 버전

WITH CTE AS (
	SELECT customer_id,
		IF(event_type = 'WITHDRAW','INACTIVE','ACTIVE') AS status,
		event_date AS start_date
	FROM membership_history mh 
	ORDER BY customer_id, start_date
) 
SELECT *, 
	DATE_SUB(LEAD (start_date) OVER (PARTITION BY customer_id ORDER BY start_date), INTERVAL 1 DAY) AS end_date,
	IF(status = 'ACTIVE' AND DATE_SUB(LEAD (start_Date) OVER (PARTITION BY customer_id ORDER BY start_Date), INTERVAL 1 DAY) IS NULL, 1, 0) AS current_flag
FROM CTE;

📍 point

• CTE 활용 여부는 중요하지 X
• IF/CASE WHEN 절을 활용하여 (JOIN, WITHDRAW)를 (ACTIVE, INACTIVE)로 변경
• LEAD 윈도우 함수를 통해 start_date를 하루 뒤로 미뤄 end_date를 생성 ⭐️
• 이때 ORDER BY start_date를 적어줘야 날짜가 꼬이는 일을 막을 수 있다.