Advertiser Status
ASKED IN INTERVIEWMeta
Problem Statement
Update the status of Meta advertisers. Use the provided transition rules to determine the new status for each user based on today's payment activity.
Rules:
- Output columns: user_id, updated_status.
- New + Payment = Active
- New + No Payment = Churn
- Active + Payment = Active
- Active + No Payment = Churn
- Churn + Payment = Resurrect
- Churn + No Payment = Churn
- NULL + Payment = New
- Sort by user_id ASC.
Tests your understanding of
Case Logic, Full Outer Join and Conditional Formatting
Input Tables
advertiser
| user_id(VARCHAR) | status(VARCHAR) |
|---|---|
| bing | New |
| yahoo | Churn |
| alibaba | Active |
daily_pay
| user_id(VARCHAR) | paid(DECIMAL) |
|---|---|
| bing | 500 |
| alibaba | 0 |
| target | 100 |
ad_accounts
| user_id(VARCHAR) | account_type(VARCHAR) |
|---|---|
| bing | Business |
Expected Output
| user_id(VARCHAR) | updated_status(VARCHAR) |
|---|---|
| alibaba | Churn |
| bing | Active |
| target | New |
| yahoo | Churn |
Tags
HardASKED IN INTERVIEWCase LogicFull Outer JoinConditional Formatting
20-30 min
41%