ch04.자동차 매출 데이터를 이용한 리포트 작성(2)

교재 소개

작성자는 멀티캠퍼스 데이터 분석 & 엔지니어 취업캠프 28회차 수강생으로 해당 게시물은 SQL로 맛보는 데이터 전처리 분석 | 노수영 - 교보문고 (kyobobook.co.kr)

SQL로 맛보는 데이터 전처리 분석 | 노수영 - 교보문고

위 교재를 참고하여 게시물을 작성함을 알린다.

 

1. 연습

SELECT
orderdate
, customernumber
, ordernumber
FROM orders;

출력결과

 

• Evan의 고객번호, 주문번호 중복 되면 큰일남

SELECT
COUNT(DISTINCT customernumber) AS 구매자수
, COUNT(ordernumber) AS 구매건수
FROM orders;

출력결과

 

---

2. 구매자 수, 구매건 수(일자별) 조회

 

SELECT
orderdate
, COUNT(DISTINCT customernumber) AS 구매자수
, COUNT(ordernumber) AS 구매건수
FROM orders
GROUP BY 1
ORDER BY 1
;

 

출력결과

 

 

---

 

3. 구매자수가 2명 이상인 날짜를 확인

• 서브쿼리 사용

SELECT A.*
FROM (
SELECT
orderdate
, COUNT(DISTINCT customernumber) AS 구매자수
, COUNT(ordernumber) AS 구매건수
FROM orders
GROUP BY 1
ORDER BY 1
) A
WHERE A.구매자수 >= 2
;

출력결과

---

4. 구매자 수, 구매건 수(연도별) 조회

• 먼저 연도별 매출액과 구매자 수를 구한다!
• 컬럼은 3개가 출력되어야 함.

SELECT
SUBSTR(A.orderdate, 1, 4) YY
, COUNT(DISTINCT A.customernumber) AS 구매자수
, SUM(priceeach * quantityordered) AS 매출액
FROM orders A
LEFT
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
GROUP BY 1
ORDER BY 1;

출력결과

---

5. 구매자 수, 구매건 수(월별) 조회

• SUBSTR()
• GROUP BY
• COUNT()
• Orders 테이블에 판매일 (Orderdate), 구매 고객 번호(Customernumber)

SELECT
SUBSTR(A.orderdate, 1, 7) MM
, COUNT(DISTINCT A.customernumber) AS 구매자수
, SUM(priceeach * quantityordered) AS 매출액
, SUM(priceeach * quantityordered) / COUNT(DISTINCT A.customernumber) AS AMV
FROM orders A
LEFT
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
GROUP BY 1
ORDER BY 1;

 

출력결과

---

6. 건당 구매 금액(ATV: Average Transaction Value)

• 1건의 거래가 평균적으로 얼마의 매출을 일으키는가?

SELECT
SUBSTR(A.orderdate, 1, 4) YY
, COUNT(DISTINCT A.ordernumber) AS 구매건수
, SUM(priceeach * quantityordered) AS 매출액
, SUM(priceeach * quantityordered) / COUNT(DISTINCT A.ordernumber) AS ATV
FROM orders A
LEFT
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
GROUP BY 1
ORDER BY 1
;

출력결과

---

7. 그룹별 구매 지표 구하기

 

SELECT *
FROM orders A
LEFT
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
LEFT
JOIN customers C
ON A.customernumber = C.customernumber
;

SELECT
C.country
, C.city
, B.priceeach * B.quantityordered
FROM orders A
LEFT
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
LEFT
JOIN customers C
ON A.customernumber = C.customernumber
;

출력결과

---

8. 국가별, 도시별 매출액 계산

 

SELECT
C.country
, C.city
, SUM(B.priceeach * B.quantityordered) AS 매출액
FROM orders A
LEFT
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
LEFT
JOIN customers C
ON A.customernumber = C.customernumber
GROUP BY 1, 2
ORDER BY 1, 2
;

출력결과

---

9. 북미 vs 비북미 매출액 비교

 

• 연습

SELECT
CASE WHEN country IN ('USA', 'Canada') THEN '북미'
ELSE '비북미'
END country_grp
, country
FROM customers;

SELECT
CASE WHEN country IN ('USA', 'Canada') THEN '북미'
ELSE '비북미'
END country_grp
, SUM(B.priceeach * B.quantityordered) AS 매출액
FROM orders A
LEFT
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
LEFT
JOIN customers C
ON A.customernumber = C.customernumber
GROUP BY 1
ORDER BY 1 ASC
;

출력결과

 

---

10.매출 Top5 국가 및 매출

DROP TABLE stat;

SELECT
quantityordered
, RANK() OVER(ORDER BY quantityordered DESC) AS 'RANK'
, DENSE_RANK() OVER(ORDER BY quantityordered DESC) AS 'DENSE RANK'
, ROW_NUMBER() OVER(ORDER BY quantityordered DESC) AS 'ROW NUMBER'
FROM orderdetails;

CREATE TABLE stat AS
SELECT
C.country
, SUM(priceeach * quantityordered) 매출액
FROM orders A
LEFT
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
LEFT
JOIN customers C
ON A.customernumber = C.customernumber
GROUP BY 1
ORDER BY 2 DESC
;

SELECT * FROM stat;

SELECT
country
, 매출액
, DENSE_RANK() OVER(ORDER BY 매출액 DESC) RNK
FROM stat;

• 교재에서는 새로운 테이블을 또 생성
• 서브쿼리 통해서 구현

SELECT A.*
FROM (
SELECT
country
, 매출액
, DENSE_RANK() OVER(ORDER BY 매출액 DESC) RNK
FROM stat
) A
WHERE RNK BETWEEN 1 AND 5
;

출력결과

 

---

11. 재구매율(Retention Rate(%), 셀프 조인

SELECT
A.customernumber
, A.orderdate
, B.customernumber
, B.orderdate
FROM orders A
LEFT
JOIN orders B
ON A.customernumber = B.customernumber
AND SUBSTR(A.orderdate, 1, 4) = SUBSTR(B.orderdate, 1, 4) - 1;

SELECT SUBSTR('2004-11-05', 1, 4) - 1; -- 실제 같은해에 주문한 이력이 존재하는가?

• A 국가 거주 구매자 중 다음 연도에서 구매를 한 구매자의 비중으로 정의

SELECT
C.country
, SUBSTR(A.orderdate, 1, 4) YY
, COUNT(DISTINCT A.customernumber) BU_1
, COUNT(DISTINCT B.customernumber) BU_2
, COUNT(DISTINCT B.customernumber) / COUNT(DISTINCT A.customernumber) AS 재구매율
FROM orders A
LEFT
JOIN orders B
ON A.customernumber = B.customernumber
AND SUBSTR(A.orderdate, 1, 4) = SUBSTR(B.orderdate, 1, 4) - 1
LEFT
JOIN customers C
ON A.customernumber = C.customernumber
GROUP BY 1, 2
;

출력결과