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`;