문제

• 선행 커맨드

use qcc;

문제 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	평점 수

문제 2

문제 3

작성한 쿼리

/*
1번 문제
1. 2012 이후 개봉 영화: RELEASE_YEAR >= 2012
2. 잘으가 2개 이상: count(distinct genres) >= 2
3. 평점 수 최소 100개 이상
4. 개봉 연도 기준 오름차순 정렬
*/
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 
  release_year 
;

/*
2번 문제
1. 각 고객의 총 결제 금액: customerID로 groupby, sum(totaldue)
2. 결제 금액 기준으로 순위: dense_rank(순위 건너뛰지 않기 때문)
3. 결과는 총 결제 금액이 높은 순으로 내림차 정렬하되, 동일한 금액일 경우 고객 ID 기준으로 오름차 정렬하여 상위 5순위의 고객 정보를 반환
*/
WITH customer_TotalDue AS (
  SELECT 
    customerid
    , SUM(totaldue) AS TotalOrderAmount
  FROM
    SalesOrderHeader
  GROUP BY
    customerid
)
, TotalDue_rank AS (
  SELECT 
    customerid
    , TotalOrderAmount
    , DENSE_RANK() OVER (ORDER BY TotalOrderAmount DESC) AS rn
  FROM 
    customer_TotalDue
)
SELECT
  *
FROM
  TotalDue_rank
WHERE 
  rn <= 5
ORDER BY 
  TotalOrderAmount DESC
  , customerid
;

/*
3번 문제
Slowly Changing Dimension(SCD) Type 2 형식의 데이터 테이블을 생성하는 SQL 문을 작성
*/
WITH cte AS (
  SELECT 
    *
    , LEAD(event_type) OVER(PARTITION BY customer_id ORDER BY event_date) AS event_type_2
    , LEAD(event_date) OVER(PARTITION BY customer_id ORDER BY event_date) AS event_date_2
  FROM 
    membership_history 
  ORDER BY
    customer_id 
    , event_date 
)
SELECT 
  , (CASE
  	WHEN event_type = 'JOIN' THEN 'ACTIVATE'
  	WHEN event_type = 'WITHDRAW' THEN 'INACTIVATE'
  END) AS status
  , event_date AS start_date
  , event_date_2 - INTERVAL 1 DAY AS end_date
  , (CASE
  	WHEN event_type = 'JOIN' AND event_date_2 IS NULL THEN 1
  	ELSE 0
  END) AS current_flag
FROM 
  cte
;

→ 사실 3번 문제 - INTERVAL 1 DAY 빼고 내서 틀렸음… (제출하고 나서 알았다)

해설

문제 1

• 연도별 매출 구하기

select
  release_year
  , sum(revenue) revenue
from
  qcc.movies
group by
  1

• 장르 확인

select
  genres
  , JSON_LENGTH(genres)
from
  qcc.movies

• 조건 추가
  • 2012년 이후
  • 장르 2개 이상
  • 최소 100개 이상의 평점 수
  • 개봉 연도 기준 오름차 정렬
    

문제 2

• 테이블 확인

select
  *
from
  qcc.SalesOrderHeader soh

• 고객별 매출 구하기
  

• RANK와 DENSE_RANK 차이 이해하기
  

• 정렬 기준 고려
  
  

문제 3

• 테이블 확인
  

• status 설정
  

• start_date 설정
  

• end_date 만들기
  
  

• flag 정의 및 기타 조건 고려
  

QnA

• json 형식의 컬럼에 like '%},%'나 '%id%id%' 써도 되나요?
  • 가능은 하지만 이번 문제에서 원하는 형식은 아니에요~
• release_year를 날짜 형식으로 바꾸지 않아도 되나요?
  • date_parse를 쓰면 release_year를 날짜 형식으로 바꿀 수 있어요!
    (해도 되고 안 해도 됨)
• cte 하나만 써서 해보기
  

추가 공부

• 경은님이 cte 하나만 써서 푼 거 알려주셨음!

WITH A AS (
SELECT CustomerID , sum(TotalDue) TotalOrderAmount
, DENSE_RANK() OVER (ORDER BY SUM(TotalDue)DESC) rn
FROM SalesOrderHeader
GROUP BY 1
ORDER BY 2 DESC, 1
)
SELECT CustomerID, TotalOrderAmount, rn
FROM A
WHERE rn <= 5

• 3번 문제 참고할 만한 다른 사람 풀이
  

• 3번 문제 join으로 풀기 → 성공!

  • 지금처럼 데이터 양이 많지 않을 때에는 cte와 큰 차이를 보이지 않아 보이지만 JOIN의 경우 cte보다 훨씬 느림
    • JOIN KEY를 하나하나 찾아서 매칭하기 때문

SELECT 
    m1.customer_id 
  , (
  CASE
    WHEN m1.event_type = 'join' THEN 'ACTIVATE'
    WHEN m1.event_type = 'withdraw' THEN 'INACTIVATE'
  END
  ) AS status
  , m1.event_date AS start_date
  , DATE_SUB(MIN(m2.event_date), INTERVAL 1 DAY) AS end_date
  , (
  CASE
  	WHEN m1.event_type = 'join' AND m2.event_date IS NULL THEN 1
  	ELSE 0
  END
  ) AS current_flag
FROM 
  membership_history m1
  LEFT JOIN membership_history m2
  ON m1.customer_id = m2.customer_id
  AND m1.event_date < m2.event_date 
  AND m1.event_type <> m2.event_type
GROUP BY 
  m1.customer_id 
  , status
  , start_date
  , current_flag
;