[QCC] Query Challenge Cycle

문제 1

 

• 테이블 설명 :

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

컬럼명 타입 설명

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

---

• 분석해야 할 내용은 다음과 같습니다 :

---

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

---

출력 값 예시

movies 테이블이 다음과 같다면 :

movie_id genres title popularity release_year revenue vote_average vote_count

1	[{“id”: 28, “name”: “Action”}, {“id”: 35, “name”: “Comedy”}]	Movie H	11	2010	100	10	1000
2	[{“id”: 28, “name”: “Action”}]	Movie A	10	2020	1000	8.5	100
3	[{“id”: 28, “name”: “Action”}, {“id”: 35, “name”: “Comedy”}]	Movie B	12	2020	1000	9	120
4	[{“id”: 28, “name”: “Action”}, {“id”: 35, “name”: “Comedy”}]	Movie C	15	2021	1100	7	150
5	[{“id”: 28, “name”: “Action”}, {“id”: 35, “name”: “Comedy”}]	Movie D	12	2021	1300	8	200

• Movie H 는 2010년에 개봉해서 포함되지 않습니다.
• Movie A 는 장르가 하나만 있습니다. 결과에 포함되지 않습니다.
• Movie B, C, D는 2012년 이후에 개봉, 장르가 2개 이상, 평점 수가 100개가 넘습니다. 결과에 포함됩니다.

---

다음과 같이 결과 출력이 되어야 합니다. (해당 테이블은 예시이며, 실제 정답과 다를 수 있습니다.)

+) 힌트 : JSON_LENGTH(*) 함수로 JSON 객체나 배열의 길이를 계산할 수 있습니다.

 

select release_year,
		sum(revenue) as revenue
from qcc.movies m 
where release_year >= 2012
and vote_count >= 100
and JSON_LENGTH(genres) > 1 
group by release_year 
order by release_year 
;

 

문제 2

 

• 테이블 설명 :

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

컬럼명 타입 설명

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

---

• 분석해야 할 내용은 다음과 같습니다 :

---

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

---

출력 값 예시

SalesOrderHeader 테이블이 다음과 같다 :

SalesOrderID CustomerID OrderDate TotalDue

1	101	1/1/23	150
2	102	1/2/23	200
3	101	1/10/23	250
4	103	1/15/23	200
5	104	2/1/23	150

• CustomerID 101의 총 결제 금액은 150 + 250 = 400입니다.
• CustomerID 102와 CustomerID 103의 총 결제 금액은 200으로 동일합니다.
• CustomerID 104의 총 결제 금액은 150입니다.
• 결제 금액이 동일한 고객은 같은 순위를 가져야 하며, 순위는 건너뛰지 않습니다.

---

with sumdue as (
	select CustomerID,
			sum(TotalDue) as totalorderamount
	from Sales_SalesOrderHeader ssoh
	group by CustomerID
), result as (
	SELECT customerid,
			totalorderamount,
			DENSE_RANK() over(order by totalorderamount desc) as rn
	from sumdue
)
select *
from result
where rn <= 5
;

 

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

---

출력 값 예시

membership_history 테이블이 다음과 같다면 :

customer_id event_type event_date

101	JOIN	2023-01-01
101	WITHDRAW	2023-03-01
101	JOIN	2023-05-01
102	JOIN	2022-12-15
102	WITHDRAW	2023-02-15

1. customer_id 101:

• 2023-01-01에 가입(JOIN)하여 ACTIVE 상태가 시작됩니다.
• 2023-03-01에 탈퇴(WITHDRAW)하여 INACTIVE 상태로 변경됩니다.
• 2023-05-01에 재가입(JOIN)하여 다시 ACTIVE 상태가 됩니다.
• 가장 최신 상태는 ACTIVE이며, END_DATE는 NULL입니다. (current_flag = 1)

1. customer_id 102:

• 2022-12-15에 가입(JOIN)하여 ACTIVE 상태가 시작됩니다.
• 2023-02-15에 탈퇴(WITHDRAW)하여 INACTIVE 상태가 됩니다.
• 가장 최신 상태는 INACTIVE이며, END_DATE는 NULL입니다. (current_flag = 0)

---

with soso as (
	select *,
			DENSE_RANK() over(partition by customer_id order by event_date) as rn
	from qcc.membership_history mh 
), wow as (
	select a.customer_id,
			case when a.event_type = 'JOIN' then 'ACTIVE'
				else 'INACTIVE'
				end as status,
			a.event_date as start_date,
			DATE_sub(b.event_date, interval 1 day) as end_date
	from soso a
	left join soso b
	on a.customer_id = b.customer_id and a.rn = (b.rn - 1)
)
select *,
		case when status = 'ACTIVE' and end_date is null then 1
			when status = 'INACTIVE' and end_date is null then 0
			else 0 end current_flag
from wow
;