logo

Back to questions

Advertiser Status [Facebook SQL Interview Question]

Hard

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:

Column NameType
user_idstring
statusstring

Example Input:

user_idstatus
bingNEW
yahooNEW
alibabaEXISTING

Table:

Column NameType
user_idstring
paiddecimal

Example Input:

user_idpaid
yahoo45.00
alibaba100.00
target13.00

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:

user_idnew_status
bingCHURN
yahooEXISTING
alibabaEXISTING

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.

#StartEndCondition
1NEWEXISTINGPaid on day T
2NEWCHURNNo pay on day T
3EXISTINGEXISTINGPaid on day T
4EXISTINGCHURNNo pay on day T
5CHURNRESURRECTPaid on day T
6CHURNCHURNNo pay on day T
7RESURRECTEXISTINGPaid on day T
8RESURRECTCHURNNo pay on day T
  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.

PostgreSQL 14