SQL | QCC 5회차

1번

2012년 이후 개봉한 영화

장르 2개 이상 포함된 영화의 연도별 매출

최소 100개 이상의 평점 수를 받은 경우만

결과는 개봉 연도를 기준으로 오름차 정렬

+) 힌트 : JSON_LENGTH(*) 함수

 

내가 쓴 답 : 

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

 

정답 : 

SELECT release_year, sum(revenue) revenue
FROM 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순위의 고객 정보를 반환

+ 결제 금액이 동일한 고객은 같은 순위를 가져야 하며, 순위는 건너뛰지 않습니다.

 

내가 쓴 답 : 

# 1) 고객 별 총 결제 금액 조회

SELECT 
	customer_id as CustomerID,
	sum(total_due) as TotalOrderAmount
FROM 
	sales_order_header
	GROUP BY customer_id ;
    
    
# 2) 총 결제 금액 순위 매기기 (순위 건너뛰면 안되니까 dense_rank)

WITH a AS(
	SELECT 
		customer_id as CustomerID,
		sum(total_due) as TotalOrderAmount
	FROM 
		sales_order_header
	GROUP BY customer_id),
SELECT 
	CustomerID,
	TotalOrderAmount,
	DENSE_RANK() OVER (ORDER BY TotalOrderAmount DESC)	AS rn
FROM a


# 3) 상위 5위만 조회

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

 

정답 : 

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

 

3번

멤버십 상태 (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를 기준으로 오름차순 정렬

+) 힌트 : DATE_ADD()/DATE_SUB()

 

내가 쓴 답 : 

# 1) 컬럼 생성

SELECT 
	customer_id,
	CASE WHEN event_type ='JOIN' THEN 'ACTIVE' ELSE 'INACTIVE' END 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
FROM membership_history

# 2) 활성 상태 컬럼 생성

WITH a AS(
	SELECT 
		customer_id,
		CASE WHEN event_type ='JOIN' THEN 'ACTIVE' ELSE 'INACTIVE' END 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
	FROM membership_history)
SELECT 
	*,
	CASE WHEN status = 'ACTIVE' AND end_date IS NULL THEN 1 ELSE 0 END AS current_flag
FROM a
ORDER BY 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;