"이건 Python으로 해야 해요" — SQL로 Pivot, JSON, UTM, RFM 전부 끝내기

SELECT
  category,
  COALESCE(SUM(CASE WHEN MONTH(order_date) = 1 THEN revenue END), 0) AS jan,
  COALESCE(SUM(CASE WHEN MONTH(order_date) = 2 THEN revenue END), 0) AS feb,
  COALESCE(SUM(CASE WHEN MONTH(order_date) = 3 THEN revenue END), 0) AS mar
FROM orders
GROUP BY category;

SELECT * FROM (
  SELECT category, EXTRACT(MONTH FROM order_date) AS month, revenue
  FROM orders
  WHERE order_date >= '2024-01-01' AND order_date < '2024-04-01'
)
PIVOT (
  SUM(revenue)
  FOR month IN (1 AS jan, 2 AS feb, 3 AS mar)
)
ORDER BY category;

SELECT
  user_id,
  JSON_EXTRACT_SCALAR(event_params, '$.page_url') AS page_url,
  JSON_EXTRACT_SCALAR(event_params, '$.campaign') AS campaign,
  JSON_EXTRACT_SCALAR(event_params, '$.device') AS device
FROM events
WHERE DATE(event_timestamp) = '2024-01-15';

-- Good: CTE에서 한 번만 파싱
WITH parsed AS (
  SELECT *, JSON_EXTRACT_SCALAR(event_params, '$.page_url') AS page_url
  FROM events
)
SELECT * FROM parsed WHERE page_url LIKE '%/products%';

WITH order_items AS (
  SELECT order_id, item_json
  FROM orders, UNNEST(JSON_EXTRACT_ARRAY(items, '$')) AS item_json
)
SELECT
  order_id,
  JSON_EXTRACT_SCALAR(item_json, '$.product_id') AS product_id,
  CAST(JSON_EXTRACT_SCALAR(item_json, '$.qty') AS INT64) AS quantity
FROM order_items;

WITH url_normalized AS (
  SELECT session_id,
    LOWER(REGEXP_REPLACE(
      REGEXP_REPLACE(landing_page, r'#.*$', ''),
      r'%20', ' '
    )) AS clean_url
  FROM sessions
)
SELECT
  session_id,
  REGEXP_EXTRACT(clean_url, r'utm_source=([^&]+)') AS utm_source,
  REGEXP_EXTRACT(clean_url, r'utm_medium=([^&]+)') AS utm_medium,
  REGEXP_EXTRACT(clean_url, r'utm_campaign=([^&]+)') AS utm_campaign
FROM url_normalized;

CASE
  WHEN utm_medium IN ('cpc', 'ppc', 'paidsearch') THEN 'Paid Search'
  WHEN utm_medium IN ('email', 'newsletter') THEN 'Email'
  WHEN utm_source IN ('facebook', 'instagram') AND utm_medium = 'cpc' THEN 'Paid Social'
  WHEN utm_source IS NULL THEN 'Direct'
  ELSE 'Other'
END AS channel_group

WITH customer_metrics AS (
  SELECT
    customer_id,
    DATE_DIFF(CURRENT_DATE(), MAX(order_date), DAY) AS recency_days,
    COUNT(DISTINCT order_id) AS frequency,
    SUM(order_amount) AS monetary
  FROM orders
  WHERE order_status = 'completed'
    AND order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
  GROUP BY customer_id
),
rfm_scores AS (
  SELECT
    customer_id,
    recency_days, frequency, monetary,
    NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
    NTILE(5) OVER (ORDER BY frequency ASC) AS f_score,
    NTILE(5) OVER (ORDER BY monetary ASC) AS m_score
  FROM customer_metrics
)
SELECT
  customer_id,
  CONCAT(r_score, f_score, m_score) AS rfm_score,
  CASE
    WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
    WHEN f_score >= 4 AND m_score >= 3 THEN 'Loyal Customers'
    WHEN r_score >= 4 AND f_score <= 2 THEN 'New Customers'
    WHEN r_score <= 2 AND f_score >= 4 THEN 'At Risk'
    WHEN r_score = 1 AND f_score <= 2 THEN 'Lost'
    ELSE 'Others'
  END AS segment
FROM rfm_scores;

-- Step 1: Raw -> Clean (매일 새벽 1시)
CREATE OR REPLACE TABLE analytics.clean_events AS
WITH deduped AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingestion_timestamp DESC) AS rn
  FROM raw_events
  WHERE DATE(event_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
)
SELECT
  event_id, user_id, event_name, event_timestamp,
  JSON_EXTRACT_SCALAR(properties, '$.page_url') AS page_url,
  REGEXP_EXTRACT(LOWER(JSON_EXTRACT_SCALAR(properties, '$.page_url')), r'utm_source=([^&]+)') AS utm_source,
  CURRENT_TIMESTAMP() AS processed_at
FROM deduped WHERE rn = 1;

-- Good: COALESCE 또는 모니터링
SELECT
  SUM(COALESCE(revenue, 0)) AS total,
  COUNTIF(revenue IS NULL) AS null_count
FROM orders;

-- Good: 비즈니스 시간대 적용
SELECT DATE(event_timestamp, 'Asia/Seoul') AS date FROM events;