[SQL 분석][Ch2] 대시보드용 데이터 준비

/* 
1. IN을 활용하여 특정 상태만 집계 ('shipped', 'delivered')
2. 일자별 지표
3. +) Round 활용하여 소수 둘째 자리까지만 표시하고 SAFE 연산 사용
4. 컬럼명 영문으로 대체 (혹시 모를 인코딩 에러 방지를 위해)
*/

/* 1) 주문 1건당 주문금액, 판매 상품의 수 구하기 */
WITH tb AS (
  SELECT
    item.order_id,
    SUM(item.price) AS ord_amt,
    COUNT(item.order_item_id) AS prd_cnt
  FROM `olist.olist_order_items` AS item
  GROUP BY item.order_id
)

/* 2) 주문 정보 테이블에 1번 결과와 고객 unique ID 붙이기 (=> 테이블 조인 2번)*/
, base AS (   -- 임시 테이블 여러 개 이어서 사용 가능
  SELECT
    DATE(ord.order_approved_at) as ord_date,
    ord.order_id,
    ord.customer_id,
    cust.customer_unique_id,
    tb.ord_amt,
    tb.prd_cnt
  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  -- 의미는 x, true를 의미 / WHERE절이 길어질 경우 주석처리하기 편하기 위해 작성
    AND order_status IN ('delivered', 'shipped')
    AND order_approved_at IS NOT NULL
)

/* 3) 2번 결과 집계 */
SELECT
  ord_date,
  ROUND(SUM(ord_amt), 2) AS ord_amt,
  COUNT(DISTINCT order_id) AS ord_cnt,
  SUM(prd_cnt) AS prd_cnt,
  ROUND(IFNULL(SAFE_DIVIDE(SUM(ord_amt), COUNT(DISTINCT order_id)), 0), 2) AS avg_ord_amt,  -- 주문 당 평균 가격
  ROUND(IFNULL(SAFE_DIVIDE(SUM(prd_cnt), COUNT(DISTINCT order_id)), 0), 2) AS avg_prd_cnt,  -- 주문 당 평균 판매상품수
  ROUND(IFNULL(SAFE_DIVIDE(SUM(ord_amt), SUM(prd_cnt)), 0), 2) AS avg_price,  -- 제품 개당 평균 가격
  COUNT(DISTINCT customer_unique_id) AS cust_cnt,  -- 주문고객수
  ROUND(IFNULL(SAFE_DIVIDE(COUNT(DISTINCT order_id), COUNT(DISTINCT customer_unique_id)), 0), 2) AS cust_freq  -- 주문빈도
FROM base
GROUP BY ord_date
ORDER BY ord_date