본문 바로가기
[업무 지식]/MySQL

[QCC] Cohort-Analysis SQL

by 에디터 윤슬 2025. 1. 13.

문제 1

  • 테이블 설명 :

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

컬럼명 타입 설명

STUDENT_ID INT 학생 ID (PK)
NAME VARCHAR 학생 이름
GENDER CHAR(1) 성별 (M/F)
AGE INT 나이
SCORE DECIMAL 시험 점수
  • 분석해야 할 내용은 다음과 같습니다 :

각 성별(GENDER) 기준으로 시험 점수가 높은 상위 3명의 학생 성별, 이름과 점수를 반환하는 SQL 문을 작성하세요.

두 학생이 동점일 경우, 나이가 많은 학생을 우선합니다. 결과는 성별(GENDER) 오름차순, 순위 오름차순으로 정렬하여 출력하세요.

출력 값 예시

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

STUDENT_ID NAME GENDER AGE SCORE

1 Alice F 20 95
2 Bob M 25 90
3 Charlie M 23 92
4 Diana F 19 85
5 Eve F 22 93
6 Frank M 22 90
7 Grace F 21 89
  • 성별이 F인 경우, 시험 점수가 높은 상위 3명은 Alice(95), Eve(93), Grace(89)입니다.
  • 성별이 M인 경우, 시험 점수가 높은 상위 3명은 Charlie(92), Bob(90), Frank(90)입니다.
    • Bob 과 Frank는 동점이지만, Bob이 나이가 더 많아 먼저 출력 됩니다.

 

정답 코드

with student_ranks as (
	select *, rank() over (partition by gender order by score desc, age desc) student_rank 
	from qcc.students 
)
select GENDER, NAME, SCORE
from student_ranks
where student_rank <= 3
order by gender, student_rank

 

문제 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 고객 ID
ORDER_DATE DATETIME 주문 날짜
DUE_DATE DATETIME 결제 기한
PAID_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_TOTAL DECIMAL(8,2) 총 가격 (수량 * 가격)
  • 분석해야 할 내용은 다음과 같습니다 :

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

  • 미결제 금액 (DUE): 아직 결제되지 않은 총 금액을 계산합니다.
    • 계산 기준 : PAID_DATE 가 NULL인 주문 항목의 총 금액 합계
    • 결과는 반올림하여 정수로 반환하세요.
  • 결제 완료 금액 (PAID): 결제 완료된 총 금액
    • 계산 기준 : PAID_DATE 가 NULL이 아닌 주문 항목의 총 금액 합계
    • 결과는 반올림하여 정수로 반환하세요.

결과는 도서 제목(TITLE)을 기준으로 오름차순 정렬하세요.

출력 값 예시

books , book_orders , book_order_items 테이블이 다음과 같다면 :

books

ID TITLE AUTHOR PRICE COPIES_IN_STOCK

1 The Great Gatsby F. Scott 10.00 20
2 To Kill a Mockingbird Harper Lee 12.00 15
3 1984 George Orwell 15.00 30

book_orders

ID CUSTOMER_ID ORDER_DATE DUE_DATE PAID_DATE

1 101 2023-01-01 2023-01-07 2023-01-05
2 102 2023-01-10 2023-01-15 NULL
3 103 2023-01-20 2023-01-25 NULL
4 104 2023-01-30 2023-02-05 NULL

book_order_items

ID ORDER_ID BOOK_ID QUANTITY PRICE LINE_TOTAL

1 1 1 2 10.00 20.00
2 2 1 1 10.00 10.00
3 3 2 3 12.00 36.00
4 4 3 1 15.00 15.00
  • The Great Gatsby:
    • 주문 1: 결제 완료 (PAID_DATE가 존재하므로 PAID = $20)
    • 주문 2: 미결제 (PAID_DATE가 NULL이므로 DUE = $10)
  • To Kill a Mockingbird:
    • 주문 3: 미결제 (PAID_DATE가 NULL이므로 DUE = $36)
    • 결제 금액 없음 (PAID = $0)
  • 1984
    • 주문 4: 미결제 (PAID_DATE가 NULL이므로 DUE = $15)
    • 결제 금액 없음 (PAID = $0)

 

정답 코드

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

 

 

문제 3

  • 테이블 설명

customer_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개월 후까지의 활성 사용자 수를 추적합니다.
  1. Cohort 정의: FIRST_ORDER_MONTH
    • First Order Month: 각 고객의 첫 주문이 발생한 월을 의미합니다.
  2. COHORT_USER_COUNT:
    • 각 Cohort 그룹의 [활성 사용자] 수를 계산합니다.
      • **[활성 사용자]**는 주문을 한 고객을 의미합니다.
  3. 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명

출력 값 예시

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

ORDER_ID CUSTOMER_ID ORDER_DATE ORDER_AMOUNT

1 101 1/1/23 120.5
2 102 1/15/23 200
3 101 2/10/23 100
4 103 2/5/23 75
5 104 3/1/23 50
6 102 3/12/23 150

Cohort 2023-01 :

  • COHORT_USER_COUNT: 2명 (CUSTOMER_ID 101, 102).
  • USER_COUNT_1_MONTH_LATER: 1명 (CUSTOMER_ID 101이 2023-02에 다시 주문)
  • USER_COUNT_2_MONTH_LATER: 1명 (CUSTOMER_ID 102가 2023-03에 다시 주문)

Cohort 2023-02 :

  • COHORT_USER_COUNT: 1명 (CUSTOMER_ID 103)
  • USER_COUNT_1_MONTH_LATER: 1명 (CUSTOMER_ID 103이 2023-03에 다시 주문)
  • USER_COUNT_2_MONTH_LATER: 0명 (2023-04에 재구매 없음)

Cohort 2023-03 :

  • COHORT_USER_COUNT: 1명 (CUSTOMER_ID 104)
  • 이후 활동 없음

 

정답 코드

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;

 

코드 해석

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

(1) `cohort` 테이블
	•	고객별로 첫 주문이 발생한 달(`first_order_month`)을 계산합니다.
	•	`MIN(ORDER_DATE)`를 사용하여 각 고객의 첫 주문 날짜를 구하고, 이를 월 단위로 변환합니다.

 

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

(2) `active_orders` 테이블
	•	각 주문(`ORDER_DATE`)에 대해 고객의 첫 주문 달(`first_order_month`)과 현재 주문 달(`active_month`)을 계산합니다.
	•	`customer_orders`와 `cohort`를 조인하여 각 주문에 첫 주문 달을 추가합니다.

 

SELECT
    first_order_month,
    active_month,
    COUNT(DISTINCT CUSTOMER_ID) AS user_count
FROM active_orders
GROUP BY first_order_month, active_month

(3) `cohort_counts` 테이블
	•	각 코호트(`first_order_month`)와 활동한 달(`active_month`)에 대해 고유한 고객 수(`user_count`)를 계산합니다.

 

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;

최종 SELECT 절
최종적으로, 각 코호트의 첫 주문 달(`first_order_month`)을 기준으로 최대 12개월 동안 활동한 고객 수를 계산하여 반환합니다.
주요 내용:
	1.	`DATE_FORMAT(first_order_month, '%Y-%m') FIRST_ORDER_MONTH`:
	•	코호트의 첫 주문 달을 `YYYY-MM` 형식으로 반환합니다.
	2.	월별 활동 고객 수 계산:
	•	`CASE WHEN active_month = first_order_month THEN user_count ELSE 0 END`:
	•	활동한 달이 첫 주문 달과 같으면 해당 고객 수를 포함하고, 그렇지 않으면 포함하지 않습니다.
	•	이를 반복적으로 작성하여 첫 주문 후 최대 12개월 동안의 활동 고객 수를 각각 계산합니다.
	•	`DATE_ADD(first_order_month, INTERVAL n MONTH)`를 사용해 특정 월을 동적으로 계산합니다.
	3.	`COALESCE`:
	•	결과가 `NULL`일 경우 `0`으로 대체하여 반환합니다.
	4.	GROUP BY 및 ORDER BY:
	•	코호트의 첫 주문 달(`first_order_month`)별로 그룹화하고, 오름차순 정렬합니다.

'[업무 지식] > MySQL' 카테고리의 다른 글

[QCC] Query Challenge Cycle  (0) 2025.01.03
[recursive] Print Prime Numbers  (0) 2024.12.13
[recursive] Draw The Triangle  (0) 2024.12.12
[날짜 그룹화] SQL Project Planning  (0) 2024.12.03
[MAX() over()] Challenges  (0) 2024.11.29

관련글

티스토리툴바