Clean Rooms

-- Clean Room은 현재 UI 및 REST API를 통해 생성합니다.
-- 아래는 Clean Room에 데이터를 등록하는 개념적 흐름입니다.

-- 생성자: 공유할 테이블 준비
CREATE TABLE IF NOT EXISTS gold.customer_purchases (
  customer_id STRING,
  purchase_date DATE,
  product_category STRING,
  amount DOUBLE
);

Clean Room 생성 후:
1. "Add Collaborator" 클릭
2. 협업자의 Databricks Sharing Identifier 입력
3. 협업자에게 초대 전송

# Clean Room 노트북 템플릿 예시
# 생성자가 작성하고, 양측이 합의한 분석만 실행 가능

# 두 조직의 데이터를 매칭하여 집계 결과만 반환
result = spark.sql("""
    SELECT
        c.product_category,
        a.campaign_name,
        COUNT(DISTINCT c.customer_id) AS matched_customers,
        SUM(c.amount) AS total_revenue
    FROM creator.customer_purchases c
    JOIN collaborator.ad_impressions a
        ON c.customer_id = a.user_id
    JOIN collaborator.campaign_metadata cm
        ON a.campaign_id = cm.campaign_id
    GROUP BY c.product_category, a.campaign_name
    HAVING COUNT(DISTINCT c.customer_id) >= 50  -- 최소 집계 단위 보장
""")

# 소매업체(생성자): 구매 데이터 보유
# 광고 플랫폼(협업자): 광고 노출 데이터 보유

# Clean Room에서 실행하는 분석
overlap_analysis = spark.sql("""
    SELECT
        ad.campaign_name,
        COUNT(DISTINCT purchase.customer_id) AS converters,
        AVG(purchase.amount) AS avg_order_value,
        SUM(purchase.amount) AS total_attributed_revenue
    FROM creator.purchases purchase
    JOIN collaborator.ad_exposures ad
        ON purchase.hashed_email = ad.hashed_email
    WHERE purchase.purchase_date BETWEEN ad.exposure_date
        AND DATEADD(DAY, 14, ad.exposure_date)
    GROUP BY ad.campaign_name
    HAVING COUNT(DISTINCT purchase.customer_id) >= 100
""")

# 병원(생성자): 진료 기록 보유
# 제약사(협업자): 약물 투여 기록 보유

clinical_study = spark.sql("""
    SELECT
        pharma.drug_name,
        hospital.age_group,
        COUNT(*) AS patient_count,
        AVG(hospital.recovery_days) AS avg_recovery_days,
        STDDEV(hospital.recovery_days) AS stddev_recovery_days
    FROM creator.patient_outcomes hospital
    JOIN collaborator.drug_administration pharma
        ON hospital.anonymized_patient_id = pharma.anonymized_patient_id
    GROUP BY pharma.drug_name, hospital.age_group
    HAVING COUNT(*) >= 30  -- 통계적 유의성 및 프라이버시 보장
""")