Objective

Write a SQL query to update the payment status of advertisers based on whether they made a payment today. The status transitions follow these rules:

• If an advertiser paid today:
  • NEW becomes EXISTING
  • EXISTING stays EXISTING
  • CHURN becomes RESURRECT
  • RESURRECT becomes EXISTING
• If an advertiser did not pay today:
  • Any status becomes CHURN
• If a payer is not in the advertiser table (brand new):
  • They get the status NEW

Table Schema:

• advertiser

• daily_pay

Task Requirements:

• Use a FULL OUTER JOIN to capture all advertisers and all payers
• Apply the status transition rules using conditional logic
• New payers not in the advertiser table should appear with status NEW
• Order results by user_id in ascending order
  
  

Output columns: user_id, new_status

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

Explanation

Step 1: Combine advertisers and payers with FULL OUTER JOIN

FROM advertiser a
FULL OUTER JOIN daily_pay p
    ON a.user_id = p.user_id

A FULL OUTER JOIN ensures every advertiser and every payer appears in the result. Advertisers who did not pay today will have p.user_id as NULL. Payers who are not yet in the advertiser table will have a.user_id and a.status as NULL.

Step 2: Handle advertisers who did not pay

WHEN p.user_id IS NULL THEN 'CHURN'

If p.user_id is NULL, the advertiser has no matching row in daily_pay, meaning they did not pay today. Regardless of their current status, they transition to CHURN.

Step 3: Handle brand-new payers

WHEN a.status IS NULL THEN 'NEW'

If a.status is NULL, the payer has no matching row in the advertiser table. This is a brand-new advertiser making their first payment, so they get the status NEW.

Step 4: Handle returning churned advertisers

WHEN a.status = 'CHURN' THEN 'RESURRECT'

If a churned advertiser makes a payment today, they transition to RESURRECT. This is checked before the ELSE clause to distinguish them from other paying advertisers.

Step 5: All other paying advertisers become EXISTING

ELSE 'EXISTING'

Any advertiser who paid today and was previously NEW, EXISTING, or RESURRECT transitions to (or remains) EXISTING. The COALESCE(a.user_id, p.user_id) ensures the output always has a non-null user_id.