오늘도 기초 프로젝트를 진행하기 위해서 아침부터 머리를 굴리고... 손가락을 굴리고... kaggle 데이터를 활용해서 프로젝트를 진행하는데 너무나 깔끔해서 어떤 비교를 통해서 결과물을 가져가야할지 아직도 갈피가 잡히지 않았다. SQL을 통해서 통계나 집계를 통해 간단한 데이터 확인 작업들을 했다. 이 과정에서도 지속적으로 SQL과 가까워진 느낌이 들긴해서 얻은 것은 있다고 생각하고 있다.

재고파악

# 재고 파악
SELECT product_id,
	   quantity '기존재고',
	   sum_q '판매재고',
	   (quantity-sum_q) as 'inven'
FROM
	(SELECT p.product_ID, 
	   p.price,
	   p.quantity ,
	   sum_q
	FROM products p join (SELECT product_id , sum(quantity) as sum_q
					  FROM sales s 
				   	  group by 1) sum_quantity on p.product_ID = sum_quantity.product_id) main
ORDER BY 4 desc;

상품별 할인률

-> 왜 정가보다 실제 판매된 가격이 더 컸던걸까... 할인률인 마이너스가 나와서 당황함... 데이터가 온전치 않다.

# 상품별 할인률
SELECT p.product_ID,
	   p.product_type,
	   p.price '정가',
	   s.price_per_unit '실제판매가격',
	   round(100-(s.price_per_unit/p.price)*100, 2) as 'Discount(%)'
FROM products p join sales s on p.product_ID =s.product_id
ORDER BY 5;

객단가 구하기

-> 1인당 평균 구매비용

# 객단가 
SELECT ROUND(sum(total_pay)/count(customer_id),2) '객단가'
FROM
(SELECT customer_id,
	   sum(payment) as 'total_pay'
FROM orders o
group by 1) main

2번 이상 구매했다면 다음 구매까지 얼마나 걸렸을까?

# 재구매자 다음 주문까지의 평균 날짜 차이 계산
# 가상테이블 생성
with RN_CREATE as (
SELECT customer_id,
	  str_to_date(order_date, '%Y-%m-%d') as order_date,
	   ROW_NUMBER() over (PARTITION BY customer_id order by order_Date) as RN
FROM orders 
order by customer_id, order_date
),
 DATE_DIFF as (
	SELECT customer_id, 
		   order_date,
		   DATEDIFF(order_date, LAG(order_date) over(partition by customer_id order by order_date)) as ORDER_DIFF
	FROM RN_CREATE 
	),
	DATE_DIFF_main as (
		SELECT *
		from DATE_DIFF
		WHERE ORDER_DIFF is not null)
		
# 1. 재구매자들의 다음 주문까지의 간격
SELECT * 
FROM DATE_DIFF_main

# 2. (재구매한 고객만 집계됨)고객별 재구매 평균 간격
SELECT customer_id,
	   avg(ORDER_DIFF) as 'REORDER_DATEDIFF '
FROM DATE_DIFF_main
GROUP BY customer_id

# Last. 모든 재구매자들의 평균 재구매 간격
SELECT ROUND(avg(REORDER_DATEDIFF)) as 'All_Reorder_customer_datediff'
FROM (SELECT customer_id,
	   avg(ORDER_DIFF) as REORDER_DATEDIFF
FROM DATE_DIFF_main
GROUP BY customer_id) sub_q

1. 재구매자들의 다음 주문까지의 간격

2. (재구매한 고객만 집계됨)고객별 재구매 평균 간격

Last. 모든 재구매자들의 평균 재구매 간격

미끼상품 찾기!

# 제품별 판매 수량,유닛가격 파악
SELECT sub_q.product_id,
	   p.product_type,
	   p.product_name,
	   p.size,
	   p.colour,
	   sum_quantity,
	   unit_price
from
(
SELECT product_id , 
	   sum(quantity) as sum_quantity, avg(price_per_unit) as unit_price
FROM sales s 
group by 1
order by 2 desc
) sub_q join products p on sub_q.product_id = p.product_id
order by sum_quantity desc, unit_price;

#판매된 상품들 평균가격
SELECT round(avg(unit_price))
FROM main_view

# 평균가격보다 적은 금액으로 많이 팔린 상품
SELECT *
FROM main_view
where unit_price < 104
limit 10;

평균가격보다 적은 금액으로 많이 팔린 상품(Top_10)

구매이력이 없는 Customer 찾기!

# customer 테이블에서 order테이블 안에 판매 내역이 없는 customer 찾기
SELECT c.customer_id
FROM customers c left join orders o on c.customer_id =o.customer_id
where o.order_id is null