Back to questions
Advertiser Status [Facebook SQL Interview Question]
You're provided with two tables: the table contains information about advertisers and their respective payment status, and the table contains the current payment information for advertisers, and it only includes advertisers who have made payments.
Write a query to update the payment status of Facebook advertisers based on the information in the table. The output should include the user ID and their current payment status, sorted by the user id.
The payment status of advertisers can be classified into the following categories:
- New: Advertisers who are newly registered and have made their first payment.
- Existing: Advertisers who have made payments in the past and have recently made a current payment.
- Churn: Advertisers who have made payments in the past but have not made any recent payment.
- Resurrect: Advertisers who have not made a recent payment but may have made a previous payment and have made a payment again recently.
Before proceeding with the question, it is important to understand the possible transitions in the advertiser's status based on the payment status. The following table provides a summary of these transitions:
| # | Current Status | Updated Status | Payment on Day T |
|---|---|---|---|
| 1 | NEW | EXISTING | Paid |
| 2 | NEW | CHURN | Not paid |
| 3 | EXISTING | EXISTING | Paid |
| 4 | EXISTING | CHURN | Not paid |
| 5 | CHURN | RESURRECT | Paid |
| 6 | CHURN | CHURN | Not paid |
| 7 | RESURRECT | EXISTING | Paid |
| 8 | RESURRECT | CHURN | Not paid |
- "Current Status" column: Represents the advertiser's current status.
- "Payment Status" column: Represents the updated payment status based on the conditions
- "Payment on Day T" column: Indicates whether the advertiser made a payment on a specific day (T) or not.
The transitions between payment statuses in the provided table can be summarized as follows:
- Rows 2, 4, 6, and 8: If an advertiser does not make a payment on day T, regardless of their previous status, their payment status transitions to "CHURN" as the updated status.
- Rows 1, 3, 5, and 7: If an advertiser makes a payment on day T, the status is updated to either "EXISTING" or "RESURRECT" based on their previous status. If the previous status was "CHURN," the updated status is "RESURRECT." For any other previous status, the updated status is "EXISTING."
Table:
| Column Name | Type |
|---|---|
| user_id | string |
| status | string |
Example Input:
| user_id | status |
|---|---|
| bing | NEW |
| yahoo | NEW |
| alibaba | EXISTING |
Table:
| Column Name | Type |
|---|---|
| user_id | string |
| paid | decimal |
Example Input:
| user_id | paid |
|---|---|
| yahoo | 45.00 |
| alibaba | 100.00 |
| target | 13.00 |
Example Output:
| user_id | new_status |
|---|---|
| bing | CHURN |
| yahoo | EXISTING |
| alibaba | EXISTING |
Explanation:
- The advertiser "bing" is classified as "CHURN" because no payment record is found in the table.
- The advertiser "yahoo" is labeled as "EXISTING" since they have made a recent payment, as indicated by the presence of their payment record.
- Similarly, the advertiser "alibaba" is also classified as "EXISTING" due to their recent payment.
The dataset you are querying against may have different input & output - this is just an example!
Solution
The solution is inspired by Sunil Ingaleshwar's approach to addressing the problem.
Step 1: Merge tables using FULL OUTER JOIN
In this step, we merge the table with the table using a FULL OUTER JOIN. Read this for more insight into SQL joins.
This allows us to combine the information from both tables and ensure that all records are included, even if they don't have a matching in the other table.
The joined table will include all rows from both tables, matched where the values are equal.
Example output (showing random 5 rows out of 9 rows):
| user_id | status | user_id | paid |
|---|---|---|---|
| bing | NEW | NULL | NULL |
| yahoo | NEW | yahoo | 45.00 |
| alibaba | EXISTING | alibaba | 100.00 |
| baidu | EXISTING | NULL | NULL |
| NULL | NULL | fitdata | 25.00 |
If you were to query , you would observe that Fitdata is present in the table. However, since Fitdata is not included in the existing advertisers' data stored in the table, the corresponding entries for Fitdata in the merged table obtained through the FULL OUTER JOIN would appear as NULL.
Step 2: Assign payment status based on payment
In this step, we assign the payment status to each user based on their payment information. We use a statement to handle different conditions and assign the appropriate status.
The payment status of advertisers can be classified into the following categories:
- New: Advertisers who are newly registered and have made their first payment.
- Existing: Advertisers who have made payments in the past and have recently made a current payment.
- Churn: Advertisers who have made payments in the past but have not made any recent payment.
- Resurrect: Advertisers who have not made a recent payment but may have made a previous payment and have made a payment again recently.
Step 3: Select the user IDs with adjusted status
In this step, we select the user IDs along with their adjusted payment status by using the COALESCE function to combine the columns from both tables.
The final step is to order them in ascending order.
PostgreSQL 14