Advertiser Status [Facebook SQL Interview Question]

Write a query to update the Facebook advertiser's status using the table. is a two-column table containing the user id and their payment status based on the last payment and table has current information about their payment. Only advertisers who paid will show up in this table.

Output the user id and current payment status sorted by the user id.

Table:

Example Input:

Table:

Example Input:

Definition of advertiser status:

• New: newly registered users who made their first payment.
• Existing: users who paid previously and recently made a current payment.
• Churn: users who paid previously, but have yet to make any recent payment.
• Resurrect: users who did not pay recently but may have made a previous payment and have made payment again recently.

Example Output:

Bing's updated status is CHURN because no payment was made in the table whereas Yahoo which made a payment is updated as EXISTING.

The dataset you are querying against may have different input & output - this is just an example!

Read this before proceeding to solve the question

For better understanding of the advertiser's status, we're sharing with you a table of possible transitions based on the payment status.

1. Row 2, 4, 6, 8: As long as the user has not paid on day T, the end status is updated to CHURN regardless of the previous status.
2. Row 1, 3, 5, 7: When the user paid on day T, the end status is updated to either EXISTING or RESURRECT, depending on their previous state. RESURRECT is only possible when the previous state is CHURN. When the previous state is anything else, the status is updated to EXISTING.

Step 1:

First, we merge the and tables with .

Showing first 5 rows out of 8 rows:

Do you know why we're joining the tables using instead of (INNER) ?

Say, if we use instead, this is the output we'll get:

Bing and Baidu is no longer in the output because they have not made any payment yet and do not exist in the table. Since the question wants us to tag their payment status regardless of payment made, we have to ensure that we gather all the advertisers' payment information.

Step 2

Did you realise that one of the advertiser is missing from the table? If you query , you will notice that Fitdata is missing. That's because Fitdata is a new advertiser and its data is not reflected in the advertiser table yet.

To ensure that we have a complete table with all the existing and new advertisers, we have to merge both tables vertically.

After this, we with . This will let us know which users are new. At last, we join the results produced by these two left joins using . If paid is null, then that user have not paid at day t and if status is null then the user is a new user(who's information is not present in table but that user paid on day t, so this particular user is called as new user)

A graphical presentation of how the UNION looks:

And, how the complete query looks:

Bear in mind that the second query has the table being the left table as Fitdata advertiser is sitting in this table.

Now you should have the complete table with 9 rows:

Interpreting this table based on the transitions table:

• Fitdata is a new advertiser who has paid $25 with its new status being NEW.
• Baidu and Alibaba are existing advertisers, but only Alibaba has made a payment and remains as EXISTING whereas Baidu is CHURN.
• Since Morgan has made a payment, its new status will be changed to EXISTING.
• Target's CHURN status will be changed to RESURRECT.

Step 3:

Our final step is to assign each advertiser to its new status using a conditional CASE statement based on the payment conditions set out:

• New: users registered and made their first payment.
• Existing: users who paid previously and recently made a current payment.
• Churn: users who paid previously, but have yet to make any recent payment.
• Resurrect: users who did not pay recently but may have made a previous payment and have made payment again recently.

Results:

Solution: