[Sql분석] 요구대로 지표만들기

1. 주별 주문수와 고객수를 뽑아주세요

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
;

2. 주(state)별 주문수를 고객수 Top 2인 상파울루(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
; 

• 서브쿼리 사용

• 앞서 했듯이 임시테이블로 만들고 사용해도 동일한 결과

• 서브쿼리 안에 where 절로 인해서 SP 와 RJ 주에 해당하지 않은 경우는 NULL 값을 표시

• 그래서 마지막 where 조건이 붙음 (2가지 주에 해당하는 값들만 가져오기 위해)

3. 2017년의 도시(city)별 주문수와 고객수, 매출을 뽑아주세요

조건1. 고객수 기준 순위도 붙여주세요
조건2.전체 순위, 주(state) 내에서의 순위 둘 다 보고 싶어요
조건3.배송완료(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 sum_amt, 
  FROM `olist.olist_orders` AS ord
  LEFT JOIN `olist.olist_customers` AS cust 
    ON ord.customer_id = cust.customer_id
  INNER JOIN tb 
    ON ord.order_id = tb.order_id 
  WHERE 1=1 ---의미 없음. 나중에 필요없는 조건을 주석처리할 때 용이
    AND EXTRACT(YEAR FROM order_approved_at) = 2017
    AND order_status = 'delivered'
  GROUP BY 1, 2 --select절 순서를 숫자로 표시할 수 있음
)
SELECT 
  customer_state, 
  customer_city, 
  sum_amt, 
  cust_cnt, 
  ord_cnt, 
   
  RANK() OVER (ORDER BY cust_cnt DESC ) AS cust_rank, --전체 매출 순위
  -- RANK() OVER (PARTITION BY customer_state ORDER BY cust_cnt DESC ) AS cust_rank, 
 --- 주별 매출 순위
FROM base 
ORDER BY cust_row --전체 순위 정렬
-- ORDER BY customer_state, cust_row --주별 순위 정렬
;

4. 고객들이 우리 서비스에서 매월 평균적으로 얼마를 쓰고 있는지 알려주세요

• 객단가 = 주문빈도 * 건당 주문 금액
• 객단가 = 총매출 / 고객수

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
;

5. 고객들의 월별 구입금액에 기반해서 그룹을 나누고,
각 그룹별 비중이 변화하는지/유지되는지를 확인하려고 합니다.
월 – 고객 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
;