분석 쿼리

비즈니스 인사이트를 도출하는 분석 쿼리 예제입니다.

서울 가맹점 매출 Top 10

SELECT
    m.name            AS merchant_name,
    m.category,
    COUNT(*)          AS txn_count,
    SUM(t.amount)     AS total_sales,
    AVG(t.amount)     AS avg_sale
FROM iceberg.card.transactions t
JOIN iceberg.card.merchants m ON t.merchant_id = m.merchant_id
WHERE m.city = '서울'
  AND t.status = 'approved'
  AND CAST(t.txn_datetime AS TIMESTAMP) >= LOCALTIMESTAMP - INTERVAL '30' DAY
GROUP BY m.name, m.category
ORDER BY total_sales DESC
LIMIT 10;

신용점수 700+ 고객 월평균 결제 (Federation JOIN)

sourcedb.public.customers와 iceberg.card.transactions를 cross-catalog JOIN합니다.

SELECT
    DATE_TRUNC('month', CAST(t.txn_datetime AS TIMESTAMP)) AS month,
    COUNT(DISTINCT c.customer_id)                           AS active_customers,
    COUNT(*)                                                AS txn_count,
    CAST(AVG(t.amount) AS BIGINT)                           AS avg_amount,
    CAST(SUM(t.amount) AS BIGINT)                           AS total_amount
FROM iceberg.card.transactions t
JOIN sourcedb.public.customers c ON t.customer_id = c.customer_id
WHERE c.credit_score >= 700
  AND t.status = 'approved'
GROUP BY DATE_TRUNC('month', CAST(t.txn_datetime AS TIMESTAMP))
ORDER BY month DESC
LIMIT 12;

고객 세그먼트별 결제 분석

WITH customer_segments AS (
    SELECT
        c.customer_id,
        CASE
            WHEN c.credit_score >= 800 THEN 'VIP (800+)'
            WHEN c.credit_score >= 700 THEN 'Premium (700-799)'
            WHEN c.credit_score >= 600 THEN 'Standard (600-699)'
            ELSE 'Basic (<600)'
        END AS segment
    FROM sourcedb.public.customers c
)
SELECT
    cs.segment,
    COUNT(DISTINCT cs.customer_id)    AS customer_count,
    COUNT(*)                          AS txn_count,
    CAST(AVG(t.amount) AS BIGINT)     AS avg_amount,
    CAST(SUM(t.amount) AS BIGINT)     AS total_amount
FROM iceberg.card.transactions t
JOIN customer_segments cs ON t.customer_id = cs.customer_id
WHERE t.status = 'approved'
GROUP BY cs.segment
ORDER BY avg_amount DESC;

시간대별 거래 패턴 (24시간)

SELECT
    HOUR(CAST(t.txn_datetime AS TIMESTAMP))   AS hour_of_day,
    COUNT(*)                                   AS txn_count,
    CAST(AVG(t.amount) AS BIGINT)              AS avg_amount,
    ROUND(
        CAST(SUM(CASE WHEN t.channel IN ('online', 'mobile') THEN 1 ELSE 0 END) AS DOUBLE)
        / COUNT(*) * 100, 1
    )                                           AS online_pct
FROM iceberg.card.transactions t
WHERE t.status = 'approved'
GROUP BY HOUR(CAST(t.txn_datetime AS TIMESTAMP))
ORDER BY hour_of_day;

지역별 거래 현황

SELECT
    m.region,
    m.city,
    COUNT(DISTINCT m.merchant_id)  AS merchant_count,
    COUNT(*)                       AS txn_count,
    CAST(SUM(t.amount) AS BIGINT)  AS total_amount
FROM iceberg.card.transactions t
JOIN iceberg.card.merchants m ON t.merchant_id = m.merchant_id
WHERE t.status = 'approved'
GROUP BY m.region, m.city
ORDER BY total_amount DESC
LIMIT 20;