/ 주 별 주문수 , 고객수 /
-- 고객 정보 기준
-- 주문 상태는 필터링하지 않음
SELECT
customer_state,
COUNT(DISTINCT ord.order_id) as ord_cnt,
COUNT(DISTINCT cust.customer_unique_id) as cust_cnt
FROM olist.olist_orders as ord
LEFT JOIN olist.olist_customers as cust
ON ord.customer_id = cust.customer_id
GROUP BY customer_state
ORDER BY cust_cnt DESC
;
/ 주 별 주문수 -> 고객수 TOP2인 주(SP,RJ)만 추출 /
SELECT
ord.order_id,
cust.customer_state
FROM olist.olist_orders as ord
LEFT JOIN (SELECT customer_id,
customer_unique_id,
customer_state
FROM olist.olist_customers
WHERE customer_state in ('SP', 'RJ')) as cust
ON ord.customer_id = cust.customer_id
WHERE cust.customer_state IS NOT NULL;
/ ROW_NUMBER():중복 없는 순위, RANK() : 동점일때 중복순위 + 공동순위자가 있으면 그 수만큼 건너뜀 , DENSE_RANK : 동점일때 중복순위 + 공동순위면 건너뜀X /
/ 3개 모두 뒤에 OVER(PARTITION BY [ 구분기준 ] ORDER BY [순위를 매기는 조건]) - PARTITION은 구분기준이 있을때 사용 /
/ 2017년 도시별 주문수,고객수,매출 추출 /
-- 고객수 기준 순위
-- 전체 순위 , 주 내에서의 순위 , 배송완료(delivered)된 건만 집계
WITH tb as(
SELECT
item.order_id,
SUM(item.price) as ord_amt
FROM olist.olist_order_items as item
GROUP BY item.order_id
)
, base AS(
SELECT
cust.customer_state,
cust.customer_city,
COUNT(DISTINCT ord.order_id) as ord_cnt,
COUNT(DISTINCT cust.customer_unique_id) as cust_cnt,
SUM(tb.ord_amt) as ord_amt
FROM olist.olist_orders as ord
INNER JOIN tb
ON ord.order_id = tb.order_id
LEFT JOIN olist.olist_customers as cust
ON ord.customer_id = cust.customer_id
WHERE 1=1
AND EXTRACT(YEAR FROM order_approved_at) = 2017
AND order_status = 'delivered'
GROUP BY 1,2
)
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY customer_state ORDER BY cust_cnt DESC) as cust_rownum,
RANK() OVER(PARTITION BY customer_state ORDER BY cust_cnt DESC) as cust_rank,
DENSE_RANK() OVER(PARTITION BY customer_state ORDER BY cust_cnt DESC) as cust_d_rank,
FROM base
ORDER BY cust_rownum
;
/ 고객들이 우리 서비스에서 매월 평균적으로 얼마를 쓰는지 ( 객단가 ) /
-- ARPU : 매출 / 사용자 수
-- ARPPU : 매출 / 구매자 수
SELECT
DATE_TRUNC(DATE(ord.order_approved_at) , MONTH) as ord_month,
SUM(item.price) as ord_amt,
COUNT(DISTINCT cust.customer_unique_id) as cust_cnt,
SAFE_DIVIDE(SUM(item.price) , COUNT(DISTINCT cust.customer_unique_id)) as avg_amt
FROM olist.olist_orders as ord
INNER JOIN olist.olist_order_items as item
ON ord.order_id = item.order_id
LEFT JOIN olist.olist_customers as cust
ON ord.customer_id = cust.customer_id
GROUP BY ord_month
ORDER BY ord_month
--CASE WHEN[조건1] THEN [조건1 해당시 결과]
/
고객들의 월별 구입금액에 기반해서 그룹을 나누고 ,
각 그룹별 비중이 변화하는지/유지하는지를 확인하려고 합니다.
월 - 고객 unique ID - 해달 월의 구매금액 - 그룹 순서로 보여주세요
300 BRL 이상은 A, 150 BRL 이상 300 BRL 미만은 B , 그 외는 C
/
SELECT
DATE_TRUNC(DATE(ord.order_approved_at) , MONTH) as ord_month,
cust.customer_unique_id,
SUM(item.price) as ord_amt,
CASE WHEN SUM(item.price) >= 300 THEN 'A'
WHEN SUM(item.price) >= 150 AND SUM(item.price) < 300 THEN 'B'
ELSE 'C' END AS level
FROM olist.olist_orders as ord
INNER JOIN olist.olist_order_items as item
ON ord.order_id = item.order_id
LEFT JOIN olist.olist_customers as cust
ON ord.customer_id = cust.customer_id
WHERE 1=1
AND order_approved_at IS NOT NULL
GROUP BY 1,2
ORDER BY ord_month ASC , ord_amt DESC
tableau에서 실습
-누계 , 차이 , 비율차이 , 이동평균 , YTD총계 , YTD성장률 , 통합 성장률
-CAGR ( 통합 성장률 ) 해당 기간 동안 평균적으로 얼마나 성장했는가?
:[ 마지막 연도 금액 / 시작 연도 금액 ] ^ (1 / 기간의 수) - 1
