Advertiser Status: Facebook SQL Interview Question

문제 풀이 링크: https://datalemur.com/questions/updated-status

 

---

🧾 페이스북 광고주 결제 상태 업데이트 문제 요약

• 목적: 광고주(user_id)의 결제 상태(payment status)를 daily_pay 정보를 바탕으로 업데이트
• 조건: 최종 출력은 user_id와 갱신된 상태를 포함하며, user_id 기준으로 정렬
• 상태 분류:
  • NEW: 신규 광고주, 첫 결제 완료
  • EXISTING: 이전에도 결제했었고, 이번에도 결제한 광고주
  • CHURN: 이전에 결제했었지만, 이번엔 결제 안 한 광고주
  • RESURRECT: 과거에 결제 이력이 끊겼다가 이번에 결제한 광고주

• 규칙 정리:
  • 결제 안 했으면:
    • 이전 상태가 있으면 → CHURN
    • 이전 상태가 없고 결제도 안 한 경우는 존재할 수 없음
      → daily_pay 테이블에 존재하지 않으므로 고려 대상 아님
  • 결제 했으면:
    • 이전 상태가 CHURN이면 → RESURRECT
    • 그 외 (NEW, EXISTING, RESURRECT)이면 → EXISTING
    • 이전 상태가 없으면 → NEW (신규 등록 + 첫 결제)

---

1. 내 풀이

• WITH절(advertiser_pay) 서브쿼리 먼저 만들어서 user_id, status, paid 정리 후 메인 쿼리
• status를 중심으로 판단

WITH advertiser_pay AS (
  SELECT 
  	CASE 
    	WHEN advertiser.user_id IS NOT NULL THEN advertiser.user_id
    	WHEN advertiser.user_id IS NULL THEN daily_pay.user_id
  	END AS user_id,
  	advertiser.status,
  	daily_pay.paid
  FROM advertiser
  FULL OUTER JOIN daily_pay
    ON advertiser.user_id = daily_pay.user_id
  )
  
SELECT user_id,
  CASE 
    WHEN status IS NULL THEN 'NEW'
    WHEN status = 'CHURN' AND paid IS NOT NULL THEN 'RESURRECT'
    WHEN status IS NOT NULL AND paid IS NOT NULL THEN 'EXISTING'
    WHEN status IS NOT NULL AND paid IS NULL THEN 'CHURN'
  END AS new_status
FROM advertiser_pay
ORDER BY user_id;

---

2. 정답 풀이

• 서브쿼리 없이 바로 FULL OUTER JOIN하고 CASE 처리
  • advertiser와 daily_pay 두 테이블을 조인하므로, 어떤 테이블에는 있지만 다른 테이블에는 없는 user_id가 있을 수 있음
  • COALESCE()를 통해 첫 번째로 NULL이 아닌 값을 반환
    • 즉, advertiser.user_id가 있으면 그걸, 없으면 daily_pay.user_id를 가져옴
    • 모든 광고주의 user_id를 안전하게 반환하기 위해 사용됨
• paid 중심으로 먼저 분기

SELECT 
  COALESCE(advertiser.user_id, daily_pay.user_id) AS user_id,
  CASE 
    WHEN paid IS NULL THEN 'CHURN' 
    WHEN paid IS NOT NULL AND advertiser.status IN ('NEW','EXISTING','RESURRECT') THEN 'EXISTING'
    WHEN paid IS NOT NULL AND advertiser.status = 'CHURN' THEN 'RESURRECT'
    WHEN paid IS NOT NULL AND advertiser.status IS NULL THEN 'NEW'
  END AS new_status
FROM advertiser
FULL OUTER JOIN daily_pay
  ON advertiser.user_id = daily_pay.user_id
ORDER BY user_id;

 

---

3. 비교

• 가독성 기준 (SQL 읽기 쉽게): 첫 번째 쿼리가 초심자나 리뷰어 입장에서 이해가 조금 더 쉬움
• user_id 처리 방식은 둘 다 맞지만, COALESCE()를 쓰는 두 번째 쿼리가 훨씬 간결하고 표준적