스파르타 내일배움캠프_본캠프_data11기 김선영_TIL_Day 8

<오늘의 학습>

• SQL 성취도 평가 시험/리뷰
• SQL 코드카타
  https://tjsdud7032.tistory.com/41

[SQL 코드카타] 레벨 2 / 11~30

• Python 학습 블로그 작성: Chapter 01
  https://tjsdud7032.tistory.com/39

Chapter 1. 코딩은 무엇이고, 데이터 분석은 왜 필요한가

 

---

<학습내용 정리>

<SQL> 성취도 평가; 실습문제

문제11) 주문 리스트 라벨

CASE WHEN 활용 문제
(CASE 구문 이해 완료)

orders 테이블에서 아래 조건을 만족하는 주문만 출력하세요.

 

조건:
`order_date`가 **'2026-01-05' ~ '2026-01-15' (포함)

 

출력 컬럼:
- `order_id`
- `order_date`
- `channel`
- `order_status`
- `coupon_flag`
     - coupon_code가 NULL이면 `'no_coupon'`
     - 아니면 `'coupon_used'`
- `delivery_days_clean`
      - `delivery_days`가 NULL이면 0, 아니면 `delivery_days`

 

정렬: `order_date` 오름차순, `order_id` 오름차순

SELECT order_id
      , order_date
      , channel
      , order_status 
      , CASE WHEN coupon_code IS NULL THEN 'no_coupon'
             ELSE coupon_code
             END coupon_flag
      , CASE WHEN delivery_days IS NULL THEN 0
             ELSE delivery_days
             END delivery_days_clean
             -- ifnull(delivery_days, 0) as delivery_days_clean 이것도 가능
FROM test.orders
WHERE order_date BETWEEN '2026-01-05' AND '2026-01-15' 
ORDER BY order_date, order_id;

문제 12) 지역별 매출 요약

1. 집계함수 리스트 사용
2. JOIN
(이해 완료)

orders + order_items를 이용해서, 완료된 주문만 지역별로 요약하세요.

조건: `order_status = 'completed'`

매출(`net_revenue`) 정의:    
`unit_price * quantity * (1 - discount_rate)`
    
출력 컬럼:
    - `ship_region`
    - `order_cnt` : 지역별 **고유 주문 수**
    - `customer_cnt` : 지역별 **고유 고객 수**
    - `net_revenue` : 지역별 매출 합계

 

추가 조건(HAVING): `order_cnt >= 2` 인 지역만

 

정렬: `net_revenue` 내림차순, `ship_region` 오름차순

 

`orders` 테이블 별칭 : o   /  `order_items` 테이블 별칭 : oi

SELECT o.ship_region
       , COUNT(DISTINCT o.order_id) AS order_cnt
       , COUNT(DISTINCT o.customer_id) AS customer_cnt
       , SUM(oi.unit_price * oi.quantity * (1-oi.discount_rate))
       		AS net_revenue
FROM test.orders o
	LEFT JOIN order_items oi
	ON o.order_id = oi.order_id
WHERE o.order_status = 'completed'
GROUP BY o.ship_region
HAVING COUNT(DISTINCT o.order_id) >= 2
ORDER BY net_revenue DESC, o.ship_region;

 

문제 13) 결제 내역 붙이기

JOIN시 조건문 WHERE / ON에 붙이기
(이해 완료)

`orders`를 기준으로 결제 정보를 붙이되, 결제가 없는 주문도 결과에 남겨야 합니다.

대상 주문:

- `order_date`가 '2026-01-05' ~ '2026-01-31' (포함)
- `payments`는 `payment_status='paid'` 인 결제만** 붙이세요.

출력 컬럼:
- `order_id`, `order_status`, `order_date`
- `payment_id`, `paid_at`, `amount` (결제 없으면 NULL)

 

정렬: `order_date`, `order_id`, `paid_at` 오름차순

 

`orders` 테이블 별칭 : o   /  `payments` 테이블 별칭 : p

SELECT o.order_id, o.order_status, o.order_date,
       p.payment_id, p.paid_at, p.amount
FROM test.orders o
	LEFT JOIN test.payments p
	ON o.order_id=p.order_id
	AND p.payment_status='paid'
WHERE o.order_date BETWEEN '2026-01-05' AND '2026-01-31'
ORDER BY order_date, order_id, paid_at;

 

문제 14) SQL 카테고리 구매 고객 찾기

JOIN 여러 번 하기
WHERE IN() 서브쿼리 조건 사용하기

<오답노트>
- 사용해야 하는 테이블들이 공통 컬럼이 없을 때는 중간에 이어지는 컬럼으로 이어서 붙이면 된다.
   ex. orders ←(order_id)→ order_items ←(book_id)→ books
- 행 조건을 WHERE / ON 어디에 쓸 건지 잘 파악하기.

아래 조건을 만족하는 고객을 찾으세요.

조건:
    - 고객이 **completed 주문**을 한 적이 있고
    - 그 주문에 포함된 책 중 **`books.category = 'sql'`** 이 **하나라도** 포함

 

출력 컬럼: `customer_id`, `customer_name`, `segment`

 

정렬: `customer_id` 오름차순
- `orders` 테이블 별칭 : o   /  `order_items` 테이블 별칭 : oi  / `books` 테이블 별칭 : b / `customers` 테이블 별칭 c

 

힌트: EXISTS 또는 IN 방식 둘 다 허용

-- IN() 사용
SELECT c.customer_id, c.customer_name, c.segment
FROM test.customers c
WHERE c.customer_id IN (
	SELECT o.customer_id
	FROM test.orders o
		LEFT JOIN test.order_items oi 
		ON o.order_id = oi.order_id
		LEFT JOIN test.books b
		ON oi.book_id = b.book_id
		AND b.category = 'sql'
	WHERE o.order_status = 'completed'
	AND b.category = 'sql'
	)
ORDER BY c.customer_id;

-- EXISTS 사용
SELECT c.customer_id, c.customer_name, c.segment
FROM test.customers c
WHERE EXISTS (
	SELECT *
	FROM test.orders o
		LEFT JOIN test.order_items oi
		ON o.order_id = oi.order_id
		LEFT JOIN test.books b
		ON oi.book_id = b.book_id
	WHERE o.customer_id = c.customer_id
	AND o.order_status = 'completed'
	AND b.category = 'sql')

 

문제 15) 고객별 주문 수를 행 유지로 붙이기

포인트! 행유지 = 윈도우 함수, group by 사용 X

`orders`에서 각 주문 행(행 유지)마다, 해당 고객의 주문 수를 붙여 출력하세요.

출력 컬럼:
    - `order_id`, `customer_id`, `order_date`, `order_status`
    - `customer_order_cnt` : 고객별 주문 수

 

정렬: `customer_id`, `order_date`, `order_id` 오름차순

select
	order_id, customer_id, order_date, order_status,
	count(*) over (partition by customer_id) as customer_order_cnt
from test.orders
order by customer_id, order_date, order_id;

---

<다음주 학습>

• 파이썬 학습주간 시작
• SQL 코드카타 레벨3 시작
• 사전캠프 퀘스트 / 달리기반 문제 풀이