SELECT
COUNT(*) AS total_orders,
MIN(order_purchase_timestamp) AS first_order,
MAX(order_purchase_timestamp) AS last_order
FROM orders;
주문 상태별 분포
SELECT
order_status,
COUNT(*) AS cnt,
ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct
FROM orders
GROUP BY order_status
ORDER BY cnt DESC;
월별 주문 추이
SELECT
DATE_FORMAT(order_purchase_timestamp, '%Y-%m') AS order_month,
COUNT(*) AS order_cnt
FROM orders
WHERE order_status != 'canceled'
GROUP BY order_month
ORDER BY order_month;
📊 발견: 2017년 하반기부터 주문이 급증하고, 2018년 초 피크를 찍는 성장 패턴 확인
💰 STEP 2. 매출 파악
전체 매출 & 평균 주문금액
SELECT
ROUND(SUM(payment_value), 0) AS total_revenue,
ROUND(AVG(payment_value), 2) AS avg_order_value,
ROUND(MAX(payment_value), 2) AS max_order_value
FROM order_payments
WHERE payment_type != 'not_defined';
카테고리별 매출 TOP 10
SELECT
COALESCE(t.product_category_name_english, p.product_category_name, 'Unknown') AS category,
COUNT(DISTINCT oi.order_id) AS order_cnt,
ROUND(SUM(oi.price), 0) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
LEFT JOIN category_translation t ON p.product_category_name = t.product_category_name
GROUP BY category
ORDER BY revenue DESC
LIMIT 10;
결제수단별 비중
SELECT
payment_type,
COUNT(*) AS cnt,
ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct
FROM order_payments
GROUP BY payment_type
ORDER BY cnt DESC;
📊 발견: 신용카드(credit_card)가 압도적 1위, boleto(브라질 전통 지불방식)가 2위
🚚 STEP 3. 배송 소요일 분석
실제 배송일 vs 예상 배송일
SELECT
ROUND(AVG(DATEDIFF(order_delivered_customer_date, order_purchase_timestamp)), 1) AS avg_actual_days,
ROUND(AVG(DATEDIFF(order_estimated_delivery_date, order_purchase_timestamp)), 1) AS avg_estimated_days
FROM orders
WHERE order_delivered_customer_date IS NOT NULL;
배송 소요일 구간별 분포
SELECT
CASE
WHEN DATEDIFF(order_delivered_customer_date, order_purchase_timestamp) <= 7 THEN '7일 이내'
WHEN DATEDIFF(order_delivered_customer_date, order_purchase_timestamp) <= 14 THEN '8~14일'
WHEN DATEDIFF(order_delivered_customer_date, order_purchase_timestamp) <= 21 THEN '15~21일'
ELSE '22일 이상'
END AS delivery_range,
COUNT(*) AS cnt
FROM orders
WHERE order_delivered_customer_date IS NOT NULL
GROUP BY delivery_range
ORDER BY MIN(DATEDIFF(order_delivered_customer_date, order_purchase_timestamp));
📊 발견: 브라질은 국토가 넓어 평균 배송이 12일 이상 — 한국 익일배송과 전혀 다른 시장
⭐ STEP 4. 리뷰 분석
리뷰 점수 분포
SELECT
review_score,
COUNT(*) AS cnt,
ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct
FROM order_reviews
GROUP BY review_score
ORDER BY review_score;
배송 지연 여부 vs 리뷰 점수
SELECT
CASE
WHEN order_delivered_customer_date > order_estimated_delivery_date THEN '지연'
ELSE '정시/조기'
END AS delivery_status,
ROUND(AVG(r.review_score), 2) AS avg_review_score,
COUNT(*) AS cnt
FROM orders o
JOIN order_reviews r ON o.order_id = r.order_id
WHERE o.order_delivered_customer_date IS NOT NULL
GROUP BY delivery_status;
📊 발견: 배송 지연 시 리뷰 점수가 유의미하게 낮아짐 → 배송 SLA가 고객 만족의 핵심 지표
🩺 STEP 5. 데이터 품질 체크
분석 지표를 설계하기 전, 반드시 결측치·이상치를 확인해야 합니다.
orders 테이블 NULL 현황
SELECT
COUNT(*) AS total,
SUM(CASE WHEN order_approved_at IS NULL THEN 1 ELSE 0 END) AS null_approved,
SUM(CASE WHEN order_delivered_carrier_date IS NULL THEN 1 ELSE 0 END) AS null_carrier,
SUM(CASE WHEN order_delivered_customer_date IS NULL THEN 1 ELSE 0 END) AS null_delivered
FROM orders;
products 테이블 결측치 현황
SELECT
COUNT(*) AS total,
SUM(CASE WHEN product_category_name IS NULL THEN 1 ELSE 0 END) AS null_category,
SUM(CASE WHEN product_weight_g = 0 OR product_weight_g IS NULL THEN 1 ELSE 0 END) AS zero_weight
FROM products;
이상치 탐지 — 비정상 고액 주문
SELECT
order_id,
payment_value
FROM order_payments
WHERE payment_value > 5000
ORDER BY payment_value DESC
LIMIT 10;
📊 발견: order_delivered_customer_date NULL은 배송 미완료 주문(정상). 반면 product_weight_g = 0은 실제 결측값으로 배송비 계산 시 제외 필요
🚨 시행착오 & 트러블슈팅
❌ 오류. Unknown column 오타
Error Code: 1054. Unknown column 'payment_vlaue' in 'field list'
원인: payment_value를 payment_vlaue로 오타 해결: 컬럼명 재확인 후 수정 교훈: SQL은 오타에 관대하지 않습니다. 자동완성(Ctrl+Space)을 적극 활용하기!
💡 회고
잘한 점
ERD를 먼저 파악하고 쿼리를 작성하니 JOIN 방향을 헷갈리지 않았음
NULL을 "오류"가 아니라 "비즈니스 상태"로 해석하는 시각이 생김 (미배송 = NULL은 정상)
배송 지연과 리뷰 점수의 관계를 EDA 단계에서 미리 발견 → 4강 데이터마트 설계 방향 결정에 활용
아쉬운 점
컬럼명 오타로 Error 1054 발생 → Workbench 자동완성 습관 필요
OVER() 윈도우 함수가 처음에는 낯설었음 → 비율 계산에 매우 유용하므로 반복 연습 필요
배운 것
"NULL은 적이 아니다. NULL이 왜 생겼는지를 이해하는 것이 분석의 시작이다." order_delivered_customer_date가 NULL인 건 배송 중이거나 취소된 주문 — 이걸 모르고 AVG 돌리면 배송 소요일이 왜곡됩니다.
[과제]
아래 5개 질문에 직접 쿼리를 작성하고 숫자를 찾아보세요.
가장 주문이 많은 요일은? 월요일
신용카드 할부 평균 개월 수는? 4개월
리뷰 점수 1점 주문 중 배송 지연 비율은? 68.9
판매자 수가 가장 많은 주(state)는? SP
평균 상품 무게는 몇 g인가? 2276.3g
🔜 다음 포스팅
[Olist 이커머스 분석 #3] SQL로 분석용 데이터마트 만들기 — RFM · 배송 · 리뷰 마트 3종
본 시리즈는 Kaggle의 Olist Brazilian E-Commerce 데이터셋을 활용한 포트폴리오 프로젝트입니다.
