[QCC] qcc_4회차 해설

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

• 테이블 설명 :

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

• 내 풀이

SELECT release_year,
       SUM(revenue) as revenue
from movies 
WHERE release_year > 2012
AND  JSON_LENGTH(genres) >= 2
and vote_count >= 100
GROUP BY release_year 
ORDER by 1;

• 튜터님 풀이

SELECT release_year, sum(revenue) revenue
FROM qcc.movies 
WHERE json_length(genres) >= 2
AND vote_count >= 100
AND release_year >= 2012
GROUP BY release_year
ORDER BY release_year

-> 이후라는 말은 그 년도를 포함하는 말이기에 =를 써주는것이 맞다!🥲

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

• 테이블 설명 :

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

• 내 풀이

with rankedcutomers as(
     SELECT CustomerID,
            SUM(TotalDue)as TotalAmount,
            DENSE_RANK() over(order by SUM(TotalDue)DESC, CustomerID asc) as rn
     from SalesOrderHeader
     group by 1
     )
SELECT CustomerID,
       TotalAmount,
       rn
from rankedcutomers
WHERE rn <= 5
order by 2 desc, 1 asc;

• 튜터님 풀이

with rankedcutomers as(
     SELECT CustomerID,
            SUM(TotalDue)as TotalAmount,
            DENSE_RANK() over(order by SUM(TotalDue)DESC) as rn
     from SalesOrderHeader
     group by 1
     )
SELECT CustomerID,
       TotalAmount,
       rn
from rankedcutomers
WHERE rn <= 5
order by 2 desc, 1 asc;

-> dense_rank를 잘 썼지만, order by에 쓸데 없는 것을 넣음.....
문제 확인을 제대로 하지 않음.

문제 3
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 테이블은 고객의 멤버십 가입 및 탈퇴 이벤트를 저장합니다.
  고객이 멤버십에 가입하거나 탈퇴한 날짜가 기록됩니다.
• 내 풀이

with ranked_events as (
SELECT customer_id,
       event_type,
       event_date, 
       ROW_NUMBER() over(PARTITION by customer_id order by event_date) as rn 
from membership_history 
),
status_changes as (
SELECT customer_id,
       case 
       	   when event_type = 'JOIN' then 'ACTIVE'
       	   when event_type = 'WITHDRAW' then 'INACTIVE'
       	   else null
       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 ranked_events
),
final_status as (
select customer_id,
       status,
       start_date,
       CASE 
       	when next_event_date is not null then DATE_SUB(next_event_date, INTERVAL 1 DAY)
       	else null
       END as end_date,
       CASE 
       	  when status = 'ACTIVE' AND next_event_date is null then 1
       	  else 0
       END as current_flag
from status_changes
)
select customer_id,
       status,
       start_date,
       end_date,
       current_flag
from final_status
WHERE status is not null
order by 1, 3;
   

• 튜터님 풀이

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 qcc.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;

내 풀이: 작업 단계를 분리하여 각 단계의 의미를 명확히 드러냄.
튜터님 풀이: CTE와 불필요한 계산 단계를 최소화하여 간결하게 작성.
-> 성능적으로 튜터님의 풀이가 좀더 적합해보인다. 나 같은 경우 불필요한 부분에 대해 추가했기때문!