캐글 이커머스 관련 data csv 다운 > 빅쿼리 프로젝트 생성 후 select * from 테이블 limit 1000으로 컬럼 및 스키마 확인
DBeaver SQLite > csv 업로드 및 select * from 테이블 limit 10으로 컬럼 확인
count / min / max 활용
* 데이터가 총 몇 건이며, 어떤 기간 동안의 기록인지 확인하는 가장 기본적인 쿼리
sum / round 활용
* 각 상품별로 총 몇 개가 팔렸고, 총 매출액(수량 × 단가)이 얼마인지 집계하여 순위를 매기는 쿼리
기초 SQL
1. 국가별 총매출액과 전체 매출 대비 각 국가가 차지하는 매출 비중(%)
- 정상 거래건(Quantity > 0, CustomerID IS NOT NULL)을 대상으로 국가별 매출 합계를 집계해야 하니 where 조건 주기
- CROSS JOIN : 전체 집계 결과(단일 행)를 모든 행에 공통 참조 ex) 국가별 매출(여러행) x 전체 매출 총액(1행)
- 또한 cross join은 별도의 ON 조건문(조인 조건)을 쓰지 않고 테이블x테이블
- total_revenue CTE로 전체 총매출을 단일 값으로 구해 분모로 가지고 국가별 매출액을 비교
- CTE 정의가 완전히 끝나는 괄호 ) 뒤에는 콤마(,)를 붙이지 않고 바로 메인 쿼리(SELECT ...)를 시작
- CTE 절 쓸떄 select * from country_revenue 등 CTE 쿼리를 확인하면서 진행
2. VIP 고객 추출 (RFM 기반 상위 고객)
- 매출 기여도가 높은 최우수 고객 TOP 10 (최근 구매일, 구매 빈도, 총 구매 금액)
- 가장 최근 구매일자(MAX 일자), 총 주문건수(COUNT DISTINCT 제품넘버), 총 구매금액(SUM 양x금액)을 집계
- NULLIF(Trim(customerID), '')을 통해 공백이 제거된 결과가 빈 문자열('')이면 NULL추출이나 IS NOT NULL 추가 조건
- not null로 구분이 안되는 비회원이거나 오류 주문 ID 제외를 위해 빈문자열 및 공백 문자 제거 함수 적용 ex) != '' / Trim()
여러 and 조건은 하나로 통일도 가능
1. where Quantity > 0 and CustomerID is not null and CustomerID != '' and Trim(CustomerID)
>>>> where Quantity > 0 And NULLIF(Trim(customerID),'') IS not null
3. AOV(주문 1건당 평균 결제 금액/객단가) 및 ARPU(고객 1인당 평균 매출)
- AOV: 전체 매출 / 고유 주문건수(InvoiceNo 수)
- ARPU: 전체 매출 / 고유 고객수(CustomerID 수)
심층분석
4. 월별 매출 및 전월 대비 증액량 / 증감률 (MoM)
- 월별 총매출, 전월 매출, 전월 대비 증액량, 전월 대비 증감률(%)
다끝나면, 해당 내용으로 태블루에서 시각화 해보기!