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

<오늘의 학습>

• 10시: SQL 라이브 세션 3회차
• 14시: 직무발표
• SQL 데일리 퀘스트 & 퀴즈

---

<학습 내용 정리>

1. SQL 라이브 세션 3회차

• 처음 데이터를 볼 때 PK(기본키)로 설정할 수 있는 고유한 값이 있는 col을 파악하는 습관!
• group by + 집계함수(count, sum, avg, min, max)
  *모든 집계 함수는 null 제외 (예외: count(*))
• Having
  group by로 묶은 조건을 처리하는 함수
  MySQL에서는 Having이 마지막에 
  (Where는 그룹 묶이기 전에 필터링 / having은 그룹된 결과를 기준으로 다시 필터링)
• 조건부 집계
  : group by로 만든 그룹 안에서, '특정 조건을 만족하는 행만' 세거나 합산하는 패턴

SUM(CASE WHEN 조건 THEN 1 ELSE 0 END)-- 조건 만족 '건수'

 

---

2. SQL 라이브 세션 3회차
# 데일리 과제

**Table: order_items 을 import할 때, [Set empty strings to NULL] 항목을 체크해서 불러왔는데 시스템의 오류 때문인지 이 설정이 잘 안 먹혔던 모양이다. 문제를 푸는 내내 최종 값이 영원히 달라서 하루 종일 이 문제만 풀었다 ㅜㅜ

 

과제1)
주문 상태별 운영 지표(아이템/주문 혼합)

--주문 한 건 당 몇 개의 상품을 구매했는지
select order_status,
       COUNT(DISTINCT order_id) as orders,
       count(*) as item_rows,
       round(count(*)/COUNT(DISTINCT order_id),2)
from basic.order_items
group by order_status
having count(*)>=10
order by orders DESC;

과제2)

채널별 취소/환불율(주문 레벨, 조건부 집계)

--1차 시도
select channel,
       count(DISTINCT order_id) as total_orders,
       count(DISTINCT case when order_status='cancelled' then 1 else 0 end) as cancelled_orders,
       count(DISTINCT case when order_status='refunded' then 1 else 0 END) as refunded_orders,
       count(DISTINCT case when order_status='cancelled' then 1
                         when order_status='refunded' then 1
                         else 0 end) as cancel_or_refund_orders,
       round((count(DISTINCT case when order_status='cancelled' then 1
                                  when order_status='refunded' then 1
                                  else 0 end)/count(DISTINCT order_id))*100, 1) as cancel_or_refund_rate_pct
from basic.order_items
group by channel
having count(DISTINCT order_id)>=20
order by cancel_or_refund_rate_pct DESC, channel ASC;

--2차 시도
select channel,
       count(DISTINCT order_id) as total_orders,
       count(DISTINCT case when order_status='cancelled' then 1 else null end) as cancelled_orders,
       count(distinct case when order_status='refunded' then 1 else null end) as refunded_orders,
       count(distinct case when order_status<>'completed' then 1 else null end) as cancel_or_refund_orders,
       round(count(distinct case when order_status<>'completed' then 1 else null end)/count(DISTINCT order_id)*100, 1) as cancel_or_refund_rate_pct
from basic.order_items
group by channel
having count(DISTINCT order_id)>=20
order by cancel_or_refund_rate_pct DESC, channel ASC;

--📌최종 답변
select channel,
       count(distinct order_id) as total_orders,
       count(distinct case when order_status='cancelled' then order_id end) as cancelled_orders,
       count(distinct case when order_status='refunded' then order_id end) as refunded_orders,
       count(distinct case when order_status in ('cancelled', 'refunded') then order_id end) as cancel_or_refund_orders,
       round(count(distinct case when order_status in ('cancelled', 'refunded') then order_id end)
             /count(distinct order_id)*100
             ,1) as cancel_or_refund_rate_pct
from basic.order_items
group by channel 
having count(distinct order_id)>=20
order by cancel_or_refund_rate_pct DESC, channel ASC;

<오답노트>
count(DISTINCT case when order_status='cancelled' then 1 else null end)
- order_status가 cancelled이면 1을 그대로 반환 -> distinct 때문에 모든 행에 반환되었던 1이 중복제거 되면서 값이 1로만 나옴.
- 그렇다고 distinct를 지우면 -> 중복되는 order_id를 걸러낼 수 없음.

 

과제3) 난이도 Hell 🔥😂

지역별 쿠폰 사용률(주문 레벨 + NULL 활용)

*주문 레벨: 주문 1건을 1로 세는 기준(: order_id가 고유값 = distinct 사용)

--''값이 있는 경우
SELECT
    region,
    COUNT(DISTINCT order_id) AS total_orders,
    COUNT(DISTINCT CASE WHEN (case when coupon_code='' then null else coupon_code end) is not null
                             THEN order_id END) AS coupon_orders,
    round(COUNT(DISTINCT CASE WHEN (case when coupon_code='' then null else coupon_code end) is not null THEN order_id END)/count(distinct order_id)*100, 1) as coupon_order_pct,
    COUNT(DISTINCT case when 
    			(case when coupon_code='' then null else coupon_code end)
    			is null then order_id end) as no_coupon_orders,
    COUNT(distinct case when delivery_type='pickup' then order_id end) as pickup_orders
from basic.order_items
group by region
having COUNT(DISTINCT CASE WHEN (case when coupon_code='' then null else coupon_code end) is not null THEN order_id END) >= 5
order by coupon_order_pct DESC, coupon_orders DESC;

--''값을 null로 설정한 경우
select region,
       count(distinct order_id) as total_orders,
       count(distinct case when coupon_code is not null then order_id end) as coupon_orders,
       round(count(distinct case when coupon_code is not null then order_id end)/count(distinct order_id)*100
       		,1) as coupon_order_pct,
       count(distinct case when coupon_code is null then order_id end) as no_coupon_orders,
       count(distinct case when delivery_type='pickup' then order_id end)as pickup_orders
from basic.order_items
group by region
having  count(distinct case when coupon_code is not null then order_id end) >= 5
order by coupon_order_pct DESC, coupon_orders DESC;

<오답노트> 
coupon_code = '' 에서 ''을 null로 해야 할 경우, null의 개수를 새는 count 함수 내에서 ''을 null로 반환하는 case when문을 한번 더 사용해야 함.
COUNT(DISTINCT CASE WHEN (case when coupon_code='' then null else coupon_code end) is not null THEN order_id END) AS coupon_orders,

 

과제4)

카테고리별 “매출 + 반품 + 리뷰”(완료 주문 기반)

SELECT product_category,
       COUNT(order_id) AS completed_items,
       ROUND(SUM(unit_price*quantity*(1-discount_rate)), 0) AS net_sales,
       COUNT(distinct CASE WHEN is_returned=1 then order_id end) AS returned_items,
       round(COUNT(distinct CASE WHEN is_returned=1 then order_id end) / COUNT(order_id)*100, 1) AS return_rate_pct,
       count(review_score) as review_cnt,
       round(count(review_score)/COUNT(order_id)*100, 1) as review_rate_pct
FROM basic.order_items
WHERE order_status='completed'
GROUP BY product_category
having COUNT(distinct order_id)>=15
order by net_sales DESC;

---

# 데일리 퀴즈

## Q1. `COUNT(review_score)`는 무엇을 세나요?
A. 전체 행 수
B. `review_score`가 **NULL이 아닌 행 수**
C. `review_score`의 서로 다른 값 개수
D. `review_score`의 평균

## Q2. 다음 중 올바른 설명은?
A. `WHERE`는 집계함수 조건을 사용할 수 있다
B. `HAVING`은 그룹이 만들어지기 전에 적용된다
C. `HAVING`은 그룹(집계 결과)에 조건을 적용할 수 있다
D. `WHERE`는 그룹을 필터링한다

## Q3. 다음 쿼리가 문제가 될 수 있는 이유는?

SELECT product_category, product_name, SUM(unit_price*quantity)
FROM basic.order_items
WHERE order_status='completed'
GROUP BY product_category;

A. `SUM()`은 SELECT에서 사용할 수 없다
B. `product_name`이 GROUP BY에도 없고 집계도 아니라서
C. `WHERE`절이 없어서
D. `ORDER BY`가 없어서

 

## Q4. 다음 중 `WHERE`에 대한 설명으로 맞는 것은?
A. 그룹 생성 후에 적용된다
B. 집계함수를 참조할 수 있다
C. 행(row) 단위 필터에 사용된다
D. 그룹 단위 필터에 사용된다

## Q5. `GROUP BY` 없이 집계 함수만 쓰면 결과는 어떻게 되나요?
A. 에러가 난다
B. 각 행이 한 그룹이 된다
C. 전체 행이 하나의 집계 그룹으로 처리된다
D. DISTINCT가 자동으로 적용된다

---

3. 직무 발표 피드백

• 발표자료를 구조와 해서 시각적으로 효율적으로 표현하는 게 중요!
• 데이터를 나눈 군집별로 특징을 나눠서(브랜딩 해서) 경영진에게 확! 전달하는 게 중요하다
• 발표 어투: 말하는 것처럼 자연스럽게 vs 아나운서처럼 딱딱
• 실제 발표 환경을 파악해서 준비한 자료가 잘 보여지는지 여러 가지를 고려하기

---

<다음주 학습 계획>

• 주말 중 SQL 사전캠프 퀘스트 진행할 예정 (완료)
  https://tjsdud7032.tistory.com/17
• SQL 난이도 올라간 라이브 세션 진행 예정