[Ch2] AD HOC QUERY

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

주(state)별 침투율을 확인하고, 침투율이 특히 낮은 지역을 중심으로 침투율을 개선해보려고 합니다. 이를 위해 주별 주문수와 고객수를 뽑아주세요.

/* 주(state)별 주문수와 고객수 추출 */
-- 고객 정보 기준
-- 주문 상태는 필터링하지 않음
SELECT
  customer_state,
  COUNT(DISTINCT order_id) ord_cnt, -- 주문수
  COUNT(DISTINCT customer_unique_id) cust_cnt -- 고객수
FROM `olist.olist_orders` ord
LEFT JOIN `olist.olist_customers` cust
  ON ord.customer_id = cust.customer_id
GROUP BY customer_state
ORDER BY cust_cnt DESC
;

2. 주(state)별 주문수를 고객수 Top 2인 상파울루(SP), 리우데자네이루(RJ) 주만 뽑아주세요

서브쿼리를 이용한 join을 연습해봅시다!

SELECT
  cust.customer_state,
  COUNT(DISTINCT ord.order_id) AS ord_cnt
FROM `olist.olist_orders` AS ord
LEFT JOIN (SELECT
            customer_id,
            customer_state,
            customer_unique_id
          FROM `olist.olist_customers` cust
          WHERE customer_state IN ('SP', 'RJ')) AS cust
ON ord.customer_id = cust.customer_id
WHERE customer_state IS NOT NULL
GROUP BY 1
;

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

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

• 2가지 주에 해당하는 값들만 가져오기 위해 NULL은 빼고 불러오는 where절 작성

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

조건1. 고객수 기준 순위도 붙여주세요
조건2.전체 순위, 주(state) 내에서의 순위 둘 다 보고 싶어요
조건3.배송완료(delivered)된 건만 집계해주세요

/*
시별 주문수, 매출, 고객수 구하기
조건 1. 2017년
조건 2. 고객수 내림차순 기준 순위 제공
조건 3. 순위는 전체순위, 주(STATE)기준 순위 둘다 제공할 것
조건 4. 배송완료(order_status = '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
) -- 매출을 가져오기 (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 
  *,
  RANK() OVER (ORDER BY cust_cnt DESC ) AS cust_rank, -- 전체 순위
  RANK() OVER (PARTITION BY customer_state ORDER BY cust_cnt DESC ) AS state_cust_rank -- 주(State)별 순위
FROM base 
ORDER BY cust_rank
;

4. 우리 고객들은 우리 서비스에서 매월 평균적으로 얼마를 쓰고 있나요?

• 객단가 = 주문빈도 * 건당 주문 금액
• ARPU : Average Revenue Per User (매출 / 사용자 수)
• ARPPU : Average Revenue Per Paid User (매출 / 구매자 수)

/*
1. 월별 지표
2. +) SAFE_DIVIDE 사용 (일반 나눗셈 연산 : /)
매출 / 고객수
*/

SELECT 
  DATE_TRUNC(DATE(ord.order_approved_at), MONTH) AS ord_month,
  SUM(item.price) AS ord_amt,
  COUNT(DISTINCT customer_unique_id) AS cust_cnt,
  SAFE_DIVIDE(SUM(item.price), COUNT(DISTINCT customer_unique_id)) AS avg_amt
FROM `olist.olist_orders` ord
INNER JOIN `olist.olist_order_items` item
  ON ord.order_id = item.order_id
LEFT JOIN `olist.olist_customers` cust
  ON ord.customer_id = cust.customer_id
GROUP BY 1
ORDER BY ord_month
;

• 고객들이 사용하는 금액이 감소하고 있다면 구매빈도가 감소하는것인지 아니면 한번에 사용하는 금액이 감소하는 것인지 확인하고 그에 따라 어떤 대처를 할지 계획하기도 함!!

5. 고객들의 월별 구입금액에 기반해서 그룹을 나누고, 각 그룹별 비중이 변화하는지/유지되는지를 확인

조건)
월 – 고객uniqueID – 해당월의구매금액 – 그룹 순서
300 BRL 이상은 A, 150 BRL 이상 300 BRL 미만은 B, 그 외 C

SELECT
  DATE_TRUNC(DATE(ord.order_approved_at), MONTH) AS ord_month,
  customer_unique_id,
  SUM(price) AS ord_amt,
  CASE 
    WHEN SUM(price)  >= 300 THEN 'A'
    WHEN SUM(price) >= 150 AND SUM(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, ord_amt DESC
;

• 평균값으로만 보면 그 안에서 고객들이 어떻게 변화하는지 확인하기 어려울 때가 있는데
  그룹을 나눠서 비중의 변화를 보면 고객들의 성격이 어떻게 변하는지 함께 확인 가능!!
  • 예) 구매금액이 낮은 고객들이 증가하는지 구매금액이 높은 고객들이 증가하는지 확인 가능