[SQL] QCC 6회차

혜·2025년 3월 14일

SQL

목록 보기

22/23

문제 1

students 테이블은 학생에 대한 정보를 담고 있습니다. 테이블 구조는 다음과 같습니다.

컬럼명	타입	설명
STUDENT_ID	INT	학생 ID (PK)
NAME	VARCHAR	학생 이름
GENDER	CHAR(1)	성별(M/F)
AGE	INT	나이
SCORE	DECIMAL	시험 점수

각 성별(GENDER) 기준으로 시험 점수가 높은 상위 3명의 학생 성별, 이름과 점수를 반환하는 SQL 문을 작성하세요. 두 학생이 동점일 경우, 나이가 많은 학생을 우선합니다.
결과는 성별(GENDER) 오름차순, 순위 오름차순으로 정렬하여 출력하세요.

풀이

WITH CTE AS(
  SELECT GENDER, NAME, SCORE,
  	ROW_NUMBER() OVER (PARTITION BY GENDER ORDER BY SCORE DESC, AGE DESC) AS RNK
  FROM students s)
SELECT GENDER, NAME, SCORE
FROM CTE
WHERE RNK <= 3
ORDER BY GENDER, RNK;

📍 point

SCORE, AGE 두 가지 정렬 조건에 맞추어 적절하게 WINDOW 함수 적용하기

문제 2

books 테이블은 도서에 대한 정보를 담고 있습니다. 테이블 구조와 각 컬럼의 의미는 다음과 같습니다

컬럼명	타입	설명
ID	INT	도서 ID (PK)
TITLE	VARCHAR(255)	도서 제목
AUTHOR	VARCHAR(255)	저자
PRICE	DECIMAL(8, 2)	가격
COPIES_IN_STOCK	INT	재고 수량

book_orders 테이블은 주문에 대한 정보를 담고 있습니다. 테이블 구조와 각 컬럼의 의미는 다음과 같습니다

컬럼명	타입	설명
ID	INT	주문 ID (PK)
CUSTOMER_ID	INT(255)	고객 ID
ORDER_DATE	DATETIME	주문 날짜
DUE_DATE	DATETIME	결제 기한
PAID_DATE	DATETIME	결제 완료 날짜
CANCELED_DATE	DATETIME	취소 날짜
REFUNDED_DATE	DATETIME	환불 날짜

book_order_items 테이블은 주문 항목에 대한 정보를 담고 있습니다. 테이블 구조와 각 컬럼의 의미는 다음과 같습니다

컬럼명	타입	설명
ID	INT	주문 항목 ID (PK)
ORDER_ID	INT	주문 ID (FK)
BOOK_ID	INT	도서 ID (FK)
QUANTITY	INT	수량
PRICE	DECIMAL(8, 2)	개별 가격
LINE_T0TAL	DECIMAL(8, 2)	총 가격 (수량 * 가격)

모든 도서에 대해 도서 제목(TITLE)과 다음 정보를 반환하는 SQL 쿼리를 작성하세요 :

미결제 금액 (DUE): 아직 결제되지 않은 총 금액을 계산합니다.

계산 기준 : PAID_DATE 가 NULL인 주문 항목의 총 금액 합계

결과는 반올림하여 정수로 반환하세요.

결제 완료 금액 (PAID): 결제 완료된 총 금액
- 계산 기준 : PAID_DATE 가 NULL이 아닌 주문 항목의 총 금액 합계
- 결과는 반올림하여 정수로 반환하세요.
결과는 도서 제목(TITLE)을 기준으로 오름차순 정렬하세요.

풀이

서브쿼리를 이용한 풀이

WITH CTE AS (SELECT b.TITLE, boi.LINE_TOTAL, bo.PAID_DATE
FROM book_order_items boi LEFT JOIN book_orders bo ON boi.ORDER_ID = bo.ID 
	LEFT JOIN books b ON boi.BOOK_ID = b.ID)
SELECT TITLE,
	ROUND(COALESCE((SELECT SUM(LINE_TOTAL) FROM CTE AS CTE2 WHERE CTE.TITLE = CTE2.TITLE AND PAID_DATE IS NULL), 0), 0) AS DUE,
	ROUND(COALESCE((SELECT SUM(LINE_TOTAL) FROM CTE AS CTE2 WHERE CTE.TITLE = CTE2.TITLE AND PAID_DATE IS NOT NULL), 0), 0) AS PAID
FROM CTE
GROUP BY TITLE
ORDER BY TITLE

서브쿼리가 필요 없는 풀이

SELECT b.TITLE,
	ROUND(COALESCE(SUM((bo.PAID_DATE IS NULL) * boi.LINE_TOTAL), 0), 0) AS DUE,
	ROUND(COALESCE(SUM((bo.PAID_DATE IS NOT NULL) * boi.LINE_TOTAL), 0), 0) AS PAID
FROM book_order_items boi LEFT JOIN book_orders bo ON boi.ORDER_ID = bo.ID 
	LEFT JOIN books b ON boi.BOOK_ID = b.ID
GROUP BY b.TITLE
ORDER BY b.TITLE

📍 point

IS NULL 혹은 IS NOT NULL을 이용하면 0 또는 1이 반환된다는 점을 이용

서브쿼리를 이용한 풀이는 시간이 더 오래 소요됨

문제 3

orders 테이블은 고객의 주문 데이터를 저장합니다. 테이블 구조와 각 컬럼의 이미는 다음과 같습니다.

컬럼명	타입	설명
ORDER_ID	INT	주문 ID (PK)
CUSTOMER_ID	INT	고객 ID
ORDER_DATE	DATE	주문 날짜
ORDER_AMOUNT	DECIMAL(10,2)	주문 금액

고객의 첫 주문 월을 기준으로 Cohort 그룹을 만들고,
각 Cohort 그룹에서 시간이 지남에 따라 활성 사용자 수를 계산하는 SQL 문을 작성하세요.
USER_COUNT_1_MONTH_LATER ~ USER_COUNT_12_MONTH_LATER 까지 계산해야 합니다.

각 Cohort 그룹에 대해 1개월 후부터 12개월 후까지의 활성 사용자 수를 추적합니다.

Cohort 정의: FIRST_ORDER_MONTH

First Order Month: 각 고객의 첫 주문이 발생한 월을 의미합니다.

COHORT_USER_COUNT:

각 Cohort 그룹의 [활성 사용자] 수를 계산합니다.

[활성 사용자]는 주문을 한 고객을 의미합니다.

USER_COUNT_X_MONTHS_LATER:

해당 COHORT가 X개월 이후에 주문한 고객 수를 계산합니다.

예:

FIRST_ORDER_MONTH = 2023-01

COHORT_USER_COUNT = 50

USER_COUNT_1_MONTH_LATER = 20

USER_COUNT_2_MONTH_LATER = 33

2023-01 에 첫 주문을 한 고객이 50명,
이들 중 1개월 뒤 주문한 고객이 20명, 2개월 뒤 주문한 고객이 33명

풀이

WITH cohort AS (
    SELECT
        CUSTOMER_ID,
        DATE(DATE_FORMAT(MIN(ORDER_DATE), '%Y-%m-01')) AS first_order_month
    FROM customer_orders
    GROUP BY CUSTOMER_ID
), active_orders AS (
    SELECT
        o.CUSTOMER_ID,
        c.first_order_month,
        DATE(DATE_FORMAT(o.ORDER_DATE, '%Y-%m-01')) AS active_month
    FROM customer_orders o
    JOIN cohort c
        ON o.CUSTOMER_ID = c.CUSTOMER_ID
), cohort_counts AS (
    SELECT
        first_order_month,
        active_month,
        COUNT(DISTINCT CUSTOMER_ID) AS user_count
    FROM active_orders
    GROUP BY first_order_month, active_month
)
SELECT
    DATE_FORMAT(first_order_month, '%Y-%m') FIRST_ORDER_MONTH,
    COALESCE(SUM(CASE WHEN active_month = first_order_month THEN user_count ELSE 0 END), 0) AS COHORT_USER_COUNT,
    COALESCE(SUM(CASE WHEN active_month = DATE_ADD(first_order_month, INTERVAL 1 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_1_MONTH_LATER,
    COALESCE(SUM(CASE WHEN active_month = DATE_ADD(first_order_month, INTERVAL 2 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_2_MONTH_LATER, 
    COALESCE(SUM(CASE WHEN active_month = DATE_ADD(first_order_month, INTERVAL 3 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_3_MONTH_LATER,
    COALESCE(SUM(CASE WHEN active_month = DATE_ADD(first_order_month, INTERVAL 4 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_4_MONTH_LATER,
    COALESCE(SUM(CASE WHEN active_month = DATE_ADD(first_order_month, INTERVAL 5 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_5_MONTH_LATER,
    COALESCE(SUM(CASE WHEN active_month = DATE_ADD(first_order_month, INTERVAL 6 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_6_MONTH_LATER,
    COALESCE(SUM(CASE WHEN active_month = DATE_ADD(first_order_month, INTERVAL 7 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_7_MONTH_LATER,
    COALESCE(SUM(CASE WHEN active_month = DATE_ADD(first_order_month, INTERVAL 8 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_8_MONTH_LATER,
    COALESCE(SUM(CASE WHEN active_month = DATE_ADD(first_order_month, INTERVAL 9 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_9_MONTH_LATER,
    COALESCE(SUM(CASE WHEN active_month = DATE_ADD(first_order_month, INTERVAL 10 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_10_MONTH_LATER,
    COALESCE(SUM(CASE WHEN active_month = DATE_ADD(first_order_month, INTERVAL 11 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_11_MONTH_LATER,
    COALESCE(SUM(CASE WHEN active_month = DATE_ADD(first_order_month, INTERVAL 12 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_12_MONTH_LATER
FROM cohort_counts
GROUP BY first_order_month
ORDER BY first_order_month;

cohort

각 customer의 첫 주문 날짜 구하기

SELECT CUSTOMER_ID,
    DATE(DATE_FORMAT(MIN(ORDER_DATE), '%Y-%m-01')) AS first_order_month
FROM customer_orders
GROUP BY CUSTOMER_ID

active_orders

각 주문별 고객, 주문날짜, 해당 고객의 첫 주문 날짜 구하기 (조인하기)

WITH cohort AS (
    SELECT
        CUSTOMER_ID,
        DATE(DATE_FORMAT(MIN(ORDER_DATE), '%Y-%m-01')) AS first_order_month
    FROM customer_orders
    GROUP BY CUSTOMER_ID
), active_orders AS (
    SELECT
        o.CUSTOMER_ID,
        c.first_order_month,
        DATE(DATE_FORMAT(o.ORDER_DATE, '%Y-%m-01')) AS active_month
    FROM customer_orders o
    JOIN cohort c
        ON o.CUSTOMER_ID = c.CUSTOMER_ID
) SELECT * FROM active_orders

cohort_counts

GROUP BY를 통해 첫 주문일자 별, 주문 일자 별 유저 수 계산

WITH cohort AS (
    SELECT
        CUSTOMER_ID,
        DATE(DATE_FORMAT(MIN(ORDER_DATE), '%Y-%m-01')) AS first_order_month
    FROM customer_orders
    GROUP BY CUSTOMER_ID
), active_orders AS (
    SELECT
        o.CUSTOMER_ID,
        c.first_order_month,
        DATE(DATE_FORMAT(o.ORDER_DATE, '%Y-%m-01')) AS active_month
    FROM customer_orders o
    JOIN cohort c
        ON o.CUSTOMER_ID = c.CUSTOMER_ID
), cohort_counts AS (
    SELECT
        first_order_month,
        active_month,
        COUNT(DISTINCT CUSTOMER_ID) AS user_count
    FROM active_orders
    GROUP BY first_order_month, active_month
)
SELECT *
FROM cohort_counts