11. [SQL] 연습문제 - EDA(2)

분석 목표

E-commerce Data를 분석하여 상품별 주문 건수와 재구매율을 파악하여 사이트 운영에 참고하고자 함.
 

데이터 설명

- 데이터 자료는 포스팅 아래 쪽에 첨부 -

• Orders 테이블

컬럼 명	설명
Order_id	주문 번호 (unique)
User_id	사용자 아이디 (unique)
Eval_set	Prior (과거 구매) / Train (학습데이터) / Test (테이스데이터)
Order_number	구매순서
Order_dow	구매요일 (0:Sunday ~ 6:Saturday)
Order_hour_of_day	구매 시간
Day_since_prior_order	마지막 구매일로부터 걸린 시간 (단위: 일), 첫 구매(order_number =1)일 경우 NA

• Order_products__prior 테이블

컬럼명	설명
Order_id	주문 번호 (unique) → orders 테이블의 order_id와 연결
Product_id	상품 번호
Add_to_cart_order	장바구니에 담은 순서
Reordered	1(재구매) / 0(최초 구매)

• Products 테이블

컬럼 명	설명
Product_id	상품 번호 → order_products__prior 테이블의 product_id와 연결
Product_name	상품 이름
Arise_id	상품 카테고리 (세부)
Department_id	상품 카테고리

• Departments 테이블

컬럼 명	설명
Department_id	상품 카테고리
Department	상품 카테고리명

• Aisles 테이블

컬럼 명	설명
Aisle_id	상품 세부 카테고리
Aisle	상품 세부 카테고리명

 

연습문제

-- Q1) 전체 주문 건수를 구하라.
select count(order_id) from orders;

-- Q2) 구매자의 수를 구하라.
select count(distinct user_id) from orders;

-- Q3) 상품이름별로 몇 건의 주문이 있었는지 체크!!!!
select P.product_name, count(OP.order_id) `CNT`
from order_products__prior OP left join products P
	on OP.product_id = P.product_id
group by P.product_name
order by `CNT` desc;

-- Q4) 장바구니에 제일 먼저 담는 상품 상위 10개의 상품번호, 상품이름, 처음 담긴 횟수를 출력하라.
select A.*, P.product_name
from (select product_id, sum(case when add_to_cart_order = 1 then 1 else 0 end) `1st_CNT`
from order_products__prior
group by product_id)A left join products P
	on A.product_id = P.product_id
order by `1st_CNT` desc
limit 10;

-- Q5) 시간대 별로 주문 건수를 구하라.
select order_hour_of_day, count(order_id) `CNT`
from orders
group by 1
order by 1;

-- Q6) 첫 구매 후에 다음 구매까지 걸리는 평균 일수를 구하라.
select avg(days_since_prior_order) `Avg_Recency`
from orders
where order_number = 2;

-- Q7) 1회 주문시 평균 몇 '종류'의 상품을 같이 주문하는지 구하라.
select count(product_id)/count(distinct order_id) `UPT`
from order_products__prior;

-- Q8) 고객 1명당 평균 주문 건수를 구하라.
select count(order_id)/count(distinct user_id) `AVG_CNT`
from orders;

-- Q9) 상품별 재구매율을 구하라.
select product_id, sum(case when reordered = 1 then 1 else 0 end)/count(product_id) `Re_Ratio`
from order_products__prior
group by product_id;

-- Q10) 위의 결과에서 재구매율이 높은 순서대로 순위를 매겨라.
select A.*, row_number() over(order by `Re_Ratio` desc) `RNK`
from (select product_id, sum(case when reordered = 1 then 1 else 0 end)/count(product_id) `Re_Ratio`
from order_products__prior
group by product_id)A;

-- Q11) 위의 결과에서 상위 랭킹 100위 이내인 상품의 이름을 함께 출력하라.
select B.* , P.product_name
from (select A.*, row_number() over(order by `Re_Ratio` desc) `RNK`
from (select product_id, sum(case when reordered = 1 then 1 else 0 end)/count(product_id) `Re_Ratio`
from order_products__prior
group by product_id)A)B left join Products P
	on B.product_id = P.product_id
having `RNK` <= 100
order by `RNK`;

---

데이터 자료

Dump_eda_2sets.sql

1.13MB

 
데이터 출처 : Instacart Market Basket Analysis | Kaggle