SQL 연습(1): 이커머스 데이터 핸들링

※ 프로젝트 개요

사용한 Skills:

SUM, ROLLUP, GROUP BY, ORDER BY, COUNT, CONVERT, NVARCHAR,

ORDER TABLE, TOP, 서브쿼리, 인라인 뷰

분석의 어려움: 이상치의 다수 삽입으로 데이터 정제 time >>> 데이터 분석 time

사용한 Data: 패스트캠퍼스 상품 결제 자료 약 20만 건

---

order: 결제중 / payment: 결제 완료 / Refund: 환불 / Transaction: 확인필요

cancelled: 결제 취소중 / completed / 결제 완료 / requested:PG사에 결제 요청중

Pending: pg사의 결제 대기중 / deleted: 확인필요

transaction_amount 최종 결제 금액 = tax_free_amount

---

SELECT 
PG,METHOD,
COUNT(*) AS CNT
FROM Fact_order
GROUP BY PG, METHOD
ORDER BY PG

--CARD, NULL, VBANK, POINT, PROMOTION 순
SELECT 
METHOD,
COUNT(*) AS CNT
FROM Fact_order
GROUP BY METHOD
ORDER BY CNT DESC

--ROLLUP 
SELECT PG, METHOD, COUNT(*) AS CNT
FROM Fact_order
GROUP BY rollup(PG, METHOD)

--ORDER CATEGORY COURSE(1~17)
SELECT DISTINCT category_title FROM Fact_order

SELECT
course_title, category_title, count(*) as cnt
FROM Fact_order 
GROUP BY category_title, course_title

--프로그래밍 34430, 데이터사이언스 14076, 업무 생산성 13323, 디자인 12009
SELECT
category_title, count(*) as cnt
FROM Fact_order 
GROUP BY category_title ORDER BY cnt DESC

SELECT
pg, method, SUM(CONVERT(BIGINT, transaction_amount)) AS total
FROM Fact_order
GROUP BY ROLLUP(pg, method)

--ORDER AND CANCELD
SELECT
--INT값 연산 0, 따라서 FLOAT로 변환
( (CANCELLED * 1.00) / (COMPLETED * 1.00) ) * 100
FROM (
SELECT 
SUM(CASE WHEN state = N'COMPLETED' THEN 1 ELSE 0 END) AS COMPLETED,
SUM(CASE WHEN state = N'CANCELLED' THEN 1 ELSE 0 END) AS CANCELLED,
SUM(1) AS TOTAL
FROM Fact_order
WHERE type in (N'ORDER', N'PAYMENT')
AND state in (N'CANCELLED', N'COMPLETED')
) AS x

--1회 결제 시 가장 비싼 강좌(B2B 강좌가 높음)
SELECT TOP 10 * FROM Fact_order
WHERE type in (N'ORDER', N'PAYMENT') AND state in (N'COMPLETED')
AND CONVERT(BIGINT, transaction_amount) > 0
ORDER BY CONVERT(BIGINT, transaction_amount) DESC

--가장 많이 수강한 사람
SELECT customer_id, count(*) as cnt,sum(CONVERT(bigint, transaction_amount)) AS transaction_amount
FROM Fact_order
WHERE type in (N'ORDER', N'PAYMENT') and state in (N'COMPLETED')
GROUP BY customer_id ORDER BY cnt desc

--1인당 평균 주문 갯수 및 금액
SELECT
(Order_cnt * 1.00) / (customer_cnt * 1.00) AS avg_order,
(transaction_amount_sum * 1.00) / (customer_cnt * 1.00) AS avg_amount,
(coupon_discount_amount_sum * 1.00) / (customer_cnt * 1.00) AS avg_coupon_amount,
(transaction_amount_sum * 1.00) / (coupon_discount_amount_sum) AS order_per_couponprice 
FROM(

SELECT
count(*) AS customer_cnt, sum(cnt) as Order_cnt, sum(transaction_amount) as transaction_amount_sum,
sum(coupon_discount_amount) as coupon_discount_amount_sum 
FROM(

SELECT
customer_id, count(*) AS cnt, sum(CONVERT(bigint, transaction_amount)) AS transaction_amount,
sum(CONVERT(float, coupon_discount_amount)) AS coupon_discount_amount
FROM Fact_order
WHERE type in (N'ORDER', N'PAYMENT') and state in (N'COMPLETED')
GROUP BY customer_id

)AS a
) AS x

--문자열 NULL 값을 Update
UPDATE Fact_order SET coupon_discount_amount = 0 WHERE ISNUMERIC(coupon_discount_amount) = 0 

--가장 많은 결제 취소자 (33건)
SELECT
customer_id, count(*) AS cnt, sum(CONVERT(bigint, transaction_amount))
FROM Fact_order
WHERE type in (N'ORDER', N'PAYMENT') and state in (N'CANCELLED')
GROUP BY customer_id ORDER BY cnt desc

SELECT * FROM Fact_order WHERE customer_id = '23171'

--월별 매출 현황 7월, 6월, 5월 순 ->7월 프로모션 ↑
SELECT
CONVERT(nvarchar(7), completed_at, 120) AS complited_at,
COUNT(*) AS cnt, SUM(CONVERT(bigint, transaction_amount)) AS transaction_amount
FROM Fact_order
WHERE type in (N'ORDER', N'PAYMENT') and state in (N'COMPLETED')
GROUP BY CONVERT(nvarchar(7), completed_at, 120)
ORDER BY CONVERT(nvarchar(7), completed_at, 120)

--요일별 결제 패턴분석
SELECT
a.month,
SUM(CASE WHEN a.weekDayName = N'일요일' Then b.transaction_amount ELSE 0 END) as [일요일],
SUM(CASE WHEN a.weekDayName = N'월요일' Then b.transaction_amount ELSE 0 END) as [월요일],
SUM(CASE WHEN a.weekDayName = N'화요일' Then b.transaction_amount ELSE 0 END) as [화요일],
SUM(CASE WHEN a.weekDayName = N'수요일' Then b.transaction_amount ELSE 0 END) as [수요일],
SUM(CASE WHEN a.weekDayName = N'목요일' Then b.transaction_amount ELSE 0 END) as [목요일],
SUM(CASE WHEN a.weekDayName = N'금요일' Then b.transaction_amount ELSE 0 END) as [금요일],
SUM(CASE WHEN a.weekDayName = N'토요일' Then b.transaction_amount ELSE 0 END) as [토요일]

FROM Dim_Date AS a inner join(

SELECT
CONVERT(nvarchar(10), completed_at, 120) AS completed_at,
category_title, subcategory_title, course_title, transaction_amount
FROM Fact_order
WHERE type in (N'ORDER', N'PAYMENT') and state in (N'COMPLETED')
) AS b ON a.date = b.completed_at
GROUP BY a.month ORDER BY a.month