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

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

/* 첫 번째 : 주문 1건당 주문금액, 판매상품의 수를 구합니다 */

WITH tb AS (
  SELECT
    item.order_id,
    SUM(item.price) AS ord_amt,
    COUNT(DISTINCT item.order_item_id) AS prd_cnt
  FROM `olist.olist_order_items` AS item
  GROUP BY item.order_id
)
/* 두 번째 : 주문 정보 테이블에 1번 결과와, 고객 unique ID를 붙입니다 */
, base AS (
  SELECT 
    DATE(ord.order_approved_at) as ord_date,
    
    ord.order_id,
    ord.order_status,
    ord.customer_id,
    cust.customer_unique_id,
    tb.ord_amt,
    tb.prd_cnt
  FROM `olist.olist_orders` ord
  LEFT JOIN `olist.olist_customers` cust
    ON ord.customer_id = cust.customer_id
  INNER JOIN tb
    ON ord.order_id = tb.order_id
  WHERE 1=1 -- 의미없는 문장으로 , WHERE절이 길어질 경우 주석처리하기 편하기 위해 작성
    AND order_status IN ('delivered', 'shipped')
    AND order_approved_at IS NOT NULL
)
/* 세 번째 : 2번 결과를 집계합니다 */

SELECT
  ord_date,
  ROUND(SUM(ord_amt), 2) AS ord_amt,
  COUNT(DISTINCT order_id) ord_cnt,
  SUM(prd_cnt) prd_cnt,
  ROUND(IFNULL(SAFE_DIVIDE(SUM(ord_amt), COUNT(DISTINCT order_id)), 0), 2) avg_ord_amt,
  ROUND(IFNULL(SAFE_DIVIDE(SUM(prd_cnt), COUNT(DISTINCT order_id)), 0), 2) avg_prd_cnt,
  ROUND(IFNULL(SAFE_DIVIDE(SUM(ord_amt), SUM(prd_cnt)), 0), 2) avg_price,
  COUNT(DISTINCT customer_unique_id) cust_cnt,
  COUNT(DISTINCT order_id) / COUNT(DISTINCT customer_unique_id) cust_freq -- 주문빈도 같은 경우는 일자별로 보는게 의미없을 수 있지만 일단은 남겨둠(날짜단위 바꿔서 사용 가능)
FROM base
GROUP BY ord_date
ORDER BY ord_date
;