Advertiser status transitions

SQL data engineering interview problem. Difficulty: advanced. Pattern: Joins. About 20 minutes. Part of the Pro drill bank.

advertiser_status stores yesterday's status for each advertiser with columns advertiser_id and status ('NEW', 'EXISTING', 'CHURN', or 'RESURRECT'). today_spend records today's payments with columns advertiser_id and paid_amount. Compute each advertiser's updated lifecycle status for today according to these transition rules: If yesterday's status was NULL and today paid > 0: 'NEW' If yesterday was 'NEW', 'EXISTING', or 'RESURRECT' and today paid > 0: 'EXISTING' If yesterday was 'NEW', 'EXISTING', or 'RESURRECT' and today paid is NULL or 0: 'CHURN' If yesterday was 'CHURN' and today paid > 0: 'RESURRECT' If yesterday was 'CHURN' and today paid is NULL or 0: 'CHURN' Return advertiser_id and new_status, ordered by advertiser_id ascending.

Requirements

• Output columns: advertiser_id, new_status.
• Order by advertiser_id ascending.

Constraints

• advertiser_id is unique in both input tables.
• status in advertiser_status is one of 'NEW', 'EXISTING', 'CHURN', 'RESURRECT'.
• paid_amount is positive when present.

Examples

Input: advertiser_status advertiser_id,status ADV1,NEW ADV2,EXISTING ADV3,CHURN ADV4,EXISTING today_spend advertiser_id,paid_amount ADV1,500 ADV2,750 ADV3,200 ADV5,1000 Output: advertiser_id | new_status ADV1 | EXISTING ADV2 | EXISTING ADV3 | RESURRECT ADV4 | CHURN ADV5 | NEW Why this passes: ADV1 was NEW and paid, so becomes EXISTING; ADV3 was CHURN but paid again, so RESURRECT; ADV4 did not pay so CHURN; ADV5 is unseen and paid so NEW.

Input: advertiser_status advertiser_id | status today_spend advertiser_id | paid_amount A1 | 50 Output: advertiser_id | new_status A1 | NEW Why this passes: No yesterday status and paid today yields 'NEW'.

Topics: lakebench, sql, full-outer-join, state-machine.

More SQL interview questions · All interview problems · Learn data engineering