SQL | QCC 6회차

1번

각 성별(GENDER) 기준

시험 점수가 높은 상위 3명의 학생 성별, 이름과 점수를 반환

두 학생이 동점일 경우, 나이가 많은 학생을 우선

결과는 성별(GENDER) 오름차순, 순위 오름차순으로 정렬

 

내가 쓴 답 : 

WITH a AS(
	SELECT
		GENDER,
		NAME,
		SCORE,
		DENSE_RANK () over (partition by gender order by score desc, age desc) as rnk
	FROM students)
SELECT GENDER, NAME, SCORE
FROM a
WHERE rnk <=3
ORDER BY GENDER, rnk ;

 

정답 : 

WITH student_ranks AS (
	SELECT *, RANK() OVER (PARTITION BY gender ORDER BY score DESC, age DESC) student_rank 
	FROM 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)을 기준으로 오름차순 정렬

 

내가 시도한 답 : 

WITH a AS
	(SELECT 
		b.TITLE,
		bo.PAID_DATE ,
		boi.QUANTITY , boi.line_total
	FROM books b 
		LEFT JOIN book_order_items boi ON b.ID = boi.BOOK_ID
		LEFT JOIN book_orders bo ON bo.ID = boi.ORDER_ID)
SELECT 
	TITLE,
	ROUND(CASE WHEN PAID_DATE IS NULL THEN SUM(line_total)
		 ELSE 0 END) AS DUE,
	ROUND(CASE WHEN PAID_DATE IS NOT NULL THEN SUM(line_total)
		 ELSE 0 END) AS PAID
FROM a
GROUP BY 1
ORDER BY 1 ;

📍 애초에 빈칸이라 최종에 null 값이 뜨는걸 해결 못함

 

정답 : 

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 books b
LEFT JOIN book_order_items oi ON b.ID = oi.BOOK_ID
LEFT JOIN book_orders o ON oi.ORDER_ID = o.ID
GROUP BY b.ID, b.TITLE
ORDER BY b.TITLE ASC;

---

3번

고객의 첫 주문 월을 기준 Cohort 그룹 생성

각 Cohort 그룹에서 시간이 지남에 따라 활성 사용자 수를 계산.

USER_COUNT_1_MONTH_LATER ~ USER_COUNT_12_MONTH_LATER 까지 계산

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

내가 시도한 답 : 

WITH a AS(
	SELECT 
		DATE_FORMAT(ORDER_DATE,'%Y-%m')  AS FIRST_ORDER_MONTH,
		ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE) AS num
	FROM customer_orders co)
SELECT 
	FIRST_ORDER_MONTH,
	COUNT(num) AS COHORT_USER_COUNT
FROM a
where num = 1
GROUP BY 1
oRDER BY 1

📍 활성 사용자 수까지는 시간도 없고 머리도 안 돌아가서 첫 주문 월 만드려고 노력했지만 실패

 

정답 : 

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;