TIL - 250222

리뷰 : 간단하게 각 상태별 변화를 CASE WHEN을 통해서 변경해주면 된다. 단, 여기서 중요한 것은 FULL OUTER JOIN으로 테이블을 병합해야 한다는 것. 오늘 처음 OUTER JOIN을 썼다. RIGHT OUTER JOIN, LEFT OUTER JOIN, FULL OUTER JOIN을 상황에 따라 잘 사용하자.

URL : https://datalemur.com/questions/updated-status

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