본문으로 건너뛰기

Federation 쿼리 모음

GenD SQL 에디터에 바로 붙여넣기할 수 있는 Federation SQL 쿼리 10개입니다.

Q1. 등급별 거래 현황​

SELECT
    c.customer_grade AS 등급,
    COUNT(*) AS 거래건수,
    SUM(t.amount) AS 총거래액,
    AVG(t.amount) AS 평균거래액
FROM sourcedb.public.customers c
JOIN iceberg.card.transactions t ON c.customer_id = t.customer_id
WHERE t.status = 'approved'
GROUP BY c.customer_grade
ORDER BY 총거래액 DESC

Q2. 고객 360 뷰 (4개 카탈로그 조인)​

SELECT
    c.name AS 고객명,
    c.customer_grade AS 등급,
    COUNT(DISTINCT t.txn_id) AS 거래건수,
    SUM(t.amount) AS 총거래액,
    COUNT(DISTINCT f.fraud_id) AS 사기의심건수,
    COUNT(DISTINCT con.consultation_id) AS 상담건수,
    s.segment_name AS 세그먼트
FROM sourcedb.public.customers c
LEFT JOIN iceberg.card.transactions t ON c.customer_id = t.customer_id
LEFT JOIN iceberg.card.fraud_labels f ON t.txn_id = f.txn_id AND f.is_fraud = true
LEFT JOIN card_crm.public.consultations con ON c.customer_id = con.customer_id
LEFT JOIN card_crm.public.customer_segments s ON c.customer_id = s.customer_id
WHERE c.customer_grade = 'VIP'
GROUP BY c.name, c.customer_grade, s.segment_name
ORDER BY 총거래액 DESC
LIMIT 20

Q3. 업종별 사기 분석​

SELECT
    m.category AS 업종,
    COUNT(*) AS 사기건수,
    SUM(t.amount) AS 사기총액,
    AVG(f.confidence_score) AS 평균신뢰도
FROM iceberg.card.fraud_labels f
JOIN iceberg.card.transactions t ON f.txn_id = t.txn_id
JOIN iceberg.card.merchants m ON t.merchant_id = m.merchant_id
WHERE f.is_fraud = true
GROUP BY m.category
ORDER BY 사기건수 DESC

Q4. 지역별 거래 현황​

SELECT
    m.region AS 지역,
    m.city AS 도시,
    COUNT(*) AS 거래건수,
    SUM(t.amount) AS 총거래액
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 총거래액 DESC
LIMIT 15

Q5. 채널별 거래 추이​

SELECT
    t.channel AS 채널,
    t.status AS 상태,
    COUNT(*) AS 건수,
    SUM(t.amount) AS 총액,
    AVG(t.amount) AS 평균액
FROM iceberg.card.transactions t
GROUP BY t.channel, t.status
ORDER BY 총액 DESC

Q6. 고위험 가맹점 거래​

SELECT
    m.name AS 가맹점명,
    m.category AS 업종,
    m.risk_score AS 위험점수,
    COUNT(*) AS 거래건수,
    SUM(t.amount) AS 거래총액
FROM iceberg.card.merchants m
JOIN iceberg.card.transactions t ON m.merchant_id = t.merchant_id
WHERE m.risk_score >= 70
GROUP BY m.name, m.category, m.risk_score
ORDER BY m.risk_score DESC
LIMIT 10

Q7. 신용등급별 연체 현황​

SELECT
    cr.risk_grade AS 위험등급,
    COUNT(DISTINCT cr.customer_id) AS 고객수,
    AVG(cr.debt_to_income) AS 평균DTI,
    SUM(cr.delinquency_count) AS 총연체건수
FROM sourcedb.public.credit_assessments cr
GROUP BY cr.risk_grade
ORDER BY cr.risk_grade

Q8. RFM 세그먼트별 거래 현황​

SELECT
    s.segment_name AS 세그먼트,
    COUNT(DISTINCT s.customer_id) AS 고객수,
    AVG(s.rfm_recency) AS 평균R,
    AVG(s.rfm_frequency) AS 평균F,
    AVG(s.rfm_monetary) AS 평균M,
    SUM(t.amount) AS 총거래액
FROM card_crm.public.customer_segments s
LEFT JOIN iceberg.card.transactions t ON s.customer_id = t.customer_id
GROUP BY s.segment_name
ORDER BY 총거래액 DESC

Q9. 상담 카테고리별 현황​

SELECT
    con.category AS 상담유형,
    con.channel AS 채널,
    COUNT(*) AS 건수,
    AVG(con.duration_seconds) AS 평균시간_초,
    SUM(CASE WHEN con.resolution = 'resolved' THEN 1 ELSE 0 END) AS 해결건수
FROM card_crm.public.consultations con
GROUP BY con.category, con.channel
ORDER BY 건수 DESC

Q10. TPC-H 벤치마크 (비교용)​

-- TPC-H Q1 변형: 주문 상태별 집계
SELECT
    o.orderstatus,
    COUNT(*) AS order_count,
    SUM(o.totalprice) AS total_price,
    AVG(o.totalprice) AS avg_price
FROM tpch.tiny.orders o
GROUP BY o.orderstatus
ORDER BY total_price DESC