Customer Classification

You must execute the code again to save those values in the database.

-- 建立訂單主表
CREATE TABLE ORDERS (
  ORDER_ID     VARCHAR2(10),  -- 訂單編號
  CUSTOMER_ID  VARCHAR2(10),  -- 顧客編號
  ORDER_DATE   DATE           -- 訂單日期
);

-- 建立訂單明細表
CREATE TABLE ORDER_ITEMS (
  ORDER_ID      VARCHAR2(10),  -- 對應的訂單編號
  PRODUCT_ID    VARCHAR2(10),  -- 商品ID
  PRODUCT_NAME  VARCHAR2(100)  -- 商品名稱
);

-- 插入訂單資料
INSERT INTO ORDERS VALUES ('1001', 'C001', TO_DATE('2025-01-01', 'YYYY-MM-DD'));
INSERT INTO ORDERS VALUES ('1002', 'C002', TO_DATE('2025-01-11', 'YYYY-MM-DD'));
INSERT INTO ORDERS VALUES ('1003', 'C003', TO_DATE('2025-02-02', 'YYYY-MM-DD'));
INSERT INTO ORDERS VALUES ('1004', 'C004', TO_DATE('2025-03-03', 'YYYY-MM-DD'));
INSERT INTO ORDERS VALUES ('1005', 'C005', TO_DATE('2025-03-23', 'YYYY-MM-DD'));
INSERT INTO ORDERS VALUES ('1006', 'C002', TO_DATE('2025-02-22', 'YYYY-MM-DD'));

-- 插入訂單品項資料
INSERT INTO ORDER_ITEMS VALUES ('1001', 'P001', 'Camera');
INSERT INTO ORDER_ITEMS VALUES ('1001', 'P002', 'Tripod');
INSERT INTO ORDER_ITEMS VALUES ('1002', 'P003', 'Laptop');
INSERT INTO ORDER_ITEMS VALUES ('1003', 'P001', 'Camera');
INSERT INTO ORDER_ITEMS VALUES ('1003', 'P003', 'Laptop');
INSERT INTO ORDER_ITEMS VALUES ('1004', 'P004', 'Coffee');
INSERT INTO ORDER_ITEMS VALUES ('1005', 'P005', 'Bicycle');
INSERT INTO ORDER_ITEMS VALUES ('1006', 'P006', 'DIY Kit');

-- 提交資料異動
COMMIT;

WITH TAG_ITEMS AS (
  SELECT
    O.CUSTOMER_ID,
    O.ORDER_DATE,
    CASE 
      WHEN UPPER(OI.PRODUCT_NAME) LIKE '%CAMERA%' THEN 'Photography Lover'
      WHEN UPPER(OI.PRODUCT_NAME) LIKE '%TRIPOD%' THEN 'Photography Lover'
      WHEN UPPER(OI.PRODUCT_NAME) LIKE '%LAPTOP%' THEN 'Tech Enthusiast'
      WHEN UPPER(OI.PRODUCT_NAME) LIKE '%COFFEE%' THEN 'Coffee Enthusiast'
      WHEN UPPER(OI.PRODUCT_NAME) LIKE '%BICYCLE%' THEN 'Fitness Lifestyle'
      WHEN UPPER(OI.PRODUCT_NAME) LIKE '%DIY%' THEN 'Craft Enthusiast'
    END AS TAG
  FROM ORDERS O
  JOIN ORDER_ITEMS OI ON O.ORDER_ID = OI.ORDER_ID
  WHERE OI.PRODUCT_NAME IS NOT NULL
),
TAG_COUNT AS (
  SELECT
    CUSTOMER_ID,
    TAG,
    COUNT(*) AS CNT,
    MIN(ORDER_DATE) AS FIRST_ORDER_DATE
  FROM TAG_ITEMS
  WHERE TAG IS NOT NULL
  GROUP BY CUSTOMER_ID, TAG
)
SELECT
  CUSTOMER_ID,
  LISTAGG(TAG || ' (' || CNT || ')', ' --> ') WITHIN GROUP (ORDER BY FIRST_ORDER_DATE) AS PURCHASE_PATH
FROM TAG_COUNT
GROUP BY CUSTOMER_ID;

CUSTOMER_I	PURCHASE_PATH
C001	Photography Lover (2)
C002	Tech Enthusiast (1) --> Craft Enthusiast (1)
C003	Photography Lover (1) --> Tech Enthusiast (1)
C004	Coffee Enthusiast (1)
C005	Fitness Lifestyle (1)

CUSTOMER_I	PURCHASE_PATH
C001	Photography Lover (2)
C002	Tech Enthusiast (1) --> Craft Enthusiast (1)
C003	Photography Lover (1) --> Tech Enthusiast (1)
C004	Coffee Enthusiast (1)
C005	Fitness Lifestyle (1)