[qcc]qcc-6회차

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

• 내 풀이

SELECT GENDER, NAME, SCORE
FROM 
(SELECT GENDER, NAME, SCORE,
       row_number() over(partition by GENDER order by SCORE desc, AGE desc) as rn
from students
) ranked_s
where rn <= 3
order by GENDER, rn;

• 튜터님 풀이

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
모든 도서에 대해 도서 제목(TITLE)과 다음 정보를 반환하는 SQL 쿼리를 작성하세요 :

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

• 내 풀이

select b.TITLE,
       round(ifnull(sum(case when bo.PAID_DATE is null then boi.LINE_TOTAL END),0))DUE,
       round(ifnull(sum(case when bo.PAID_DATE is NOT null then boi.LINE_TOTAL END),0))PAID
from books b left join book_order_items boi 
on b.ID = boi.BOOK_ID 
left join book_orders bo 
on boi.ORDER_ID = bo.ID 
GROUP BY b.ID, b.TITLE  
order by 1;

• 튜터님 풀이

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

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

• 내 풀이

WITH
customer_first_order AS (
    SELECT
        CUSTOMER_ID,
        DATE_FORMAT(MIN(ORDER_DATE), '%Y-%m') AS FIRST_ORDER_MONTH
    FROM
        customer_orders
    GROUP BY
        CUSTOMER_ID
),
orders_by_month AS (
    SELECT
        CUSTOMER_ID,
        DATE_FORMAT(ORDER_DATE, '%Y-%m') AS ORDER_MONTH
    FROM
        customer_orders
),
cohort_analysis AS (
    SELECT
        cf.FIRST_ORDER_MONTH,
        obm.ORDER_MONTH,
        TIMESTAMPDIFF(MONTH, STR_TO_DATE(cf.FIRST_ORDER_MONTH, '%Y-%m-01'), STR_TO_DATE(obm.ORDER_MONTH, '%Y-%m-01')) AS MONTH_DIFF,
        obm.CUSTOMER_ID
    FROM
        customer_first_order cf
    JOIN
        orders_by_month obm ON cf.CUSTOMER_ID = obm.CUSTOMER_ID
)
SELECT
    FIRST_ORDER_MONTH,
    COUNT(DISTINCT CASE WHEN MONTH_DIFF = 0 THEN CUSTOMER_ID END) AS COHORT_USER_COUNT,
    COUNT(DISTINCT CASE WHEN MONTH_DIFF = 1 THEN CUSTOMER_ID END) AS USER_COUNT_1_MONTH_LATER,
    COUNT(DISTINCT CASE WHEN MONTH_DIFF = 2 THEN CUSTOMER_ID END) AS USER_COUNT_2_MONTH_LATER,
    COUNT(DISTINCT CASE WHEN MONTH_DIFF = 3 THEN CUSTOMER_ID END) AS USER_COUNT_3_MONTH_LATER,
    COUNT(DISTINCT CASE WHEN MONTH_DIFF = 4 THEN CUSTOMER_ID END) AS USER_COUNT_4_MONTH_LATER,
    COUNT(DISTINCT CASE WHEN MONTH_DIFF = 5 THEN CUSTOMER_ID END) AS USER_COUNT_5_MONTH_LATER,
    COUNT(DISTINCT CASE WHEN MONTH_DIFF = 6 THEN CUSTOMER_ID END) AS USER_COUNT_6_MONTH_LATER,
    COUNT(DISTINCT CASE WHEN MONTH_DIFF = 7 THEN CUSTOMER_ID END) AS USER_COUNT_7_MONTH_LATER,
    COUNT(DISTINCT CASE WHEN MONTH_DIFF = 8 THEN CUSTOMER_ID END) AS USER_COUNT_8_MONTH_LATER,
    COUNT(DISTINCT CASE WHEN MONTH_DIFF = 9 THEN CUSTOMER_ID END) AS USER_COUNT_9_MONTH_LATER,
    COUNT(DISTINCT CASE WHEN MONTH_DIFF = 10 THEN CUSTOMER_ID END) AS USER_COUNT_10_MONTH_LATER,
    COUNT(DISTINCT CASE WHEN MONTH_DIFF = 11 THEN CUSTOMER_ID END) AS USER_COUNT_11_MONTH_LATER,
    COUNT(DISTINCT CASE WHEN MONTH_DIFF = 12 THEN CUSTOMER_ID END) AS USER_COUNT_12_MONTH_LATER
FROM
    cohort_analysis
GROUP BY
    FIRST_ORDER_MONTH
ORDER BY
    FIRST_ORDER_MONTH;

• 튜터님 풀이

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 CTE:
  각 고객의 첫 주문 월을 계산하여 first_order_month라는 기준을 설정.
  이 단계는 내 코드의 customer_first_order와 동일한 역할.

• active_orders CTE:
  모든 주문 데이터를 고객의 첫 주문 월(first_order_month)과 연결하여 주문이 발생한 월(active_month)을 정리.
  이 과정은 내 코드의 orders_by_month와 유사하지만, 직접적으로 첫 주문 월과 연결되므로 계산이 명확.

• cohort_counts CTE:
  각 Cohort 그룹(first_order_month)과 해당 월의 활성 사용자 수를 계산.
  이 단계는 내 코드의 cohort_analysis와 유사하지만, MONTH_DIFF 없이 직접적으로 월 차이를 계산.

• 최종 결과:
  active_month가 first_order_month와 같을 때, 또는 1~12개월 이후일 때 각각의 활성 사용자 수를 계산합.
  DATE_ADD 함수를 사용하여 각 월의 사용자 수를 구체적으로 나눔.