Back to questions
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.
Column Name | Type |
---|---|
user_id | string |
status | string |
user_id | status |
---|---|
bing | NEW |
yahoo | NEW |
alibaba | EXISTING |
Column Name | Type |
---|---|
user_id | string |
paid | decimal |
user_id | paid |
---|---|
yahoo | 45.00 |
alibaba | 100.00 |
target | 13.00 |
Definition of advertiser status:
user_id | new_status |
---|---|
bing | CHURN |
yahoo | EXISTING |
alibaba | EXISTING |
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.
# | Start | End | Condition |
---|---|---|---|
1 | NEW | EXISTING | Paid on day T |
2 | NEW | CHURN | No pay on day T |
3 | EXISTING | EXISTING | Paid on day T |
4 | EXISTING | CHURN | No pay on day T |
5 | CHURN | RESURRECT | Paid on day T |
6 | CHURN | CHURN | No pay on day T |
7 | RESURRECT | EXISTING | Paid on day T |
8 | RESURRECT | CHURN | No pay on day T |
Step 1:
First, we merge the and tables with .
Showing first 5 rows out of 8 rows:
user_id | status | paid |
---|---|---|
bing | NEW | |
yahoo | NEW | 45.00 |
alibaba | EXISTING | 100.00 |
baidu | EXISTING | |
target | CHURN | 13.00 |
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:
user_id | status | paid |
---|---|---|
yahoo | NEW | 45.00 |
alibaba | EXISTING | 100.00 |
target | CHURN | 13.00 |
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:
user_id | status | paid |
---|---|---|
fitdata | 25.00 | |
baidu | EXISTING | |
morgan | RESURRECT | 600.00 |
target | CHURN | 13.00 |
alibaba | EXISTING | 100.00 |
Interpreting this table based on the transitions table:
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:
Results:
user_id | new_status |
---|---|
fitdata | NEW |
baidu | CHURN |
morgan | EXISTING |
target | RESURRECT |
alibaba | EXISTING |
Solution:
PostgreSQL 14