Write SQL to rank advertisers and profitability
Company: Instacart
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given two denormalized tables. Use ANSI SQL (CTEs allowed) and UNPIVOT (or an equivalent UNION ALL approach if UNPIVOT is unavailable).
Table: advertiser_spend
Columns: advertiser_id INT, advertiser_name VARCHAR, spend_prog_A DECIMAL, spend_prog_B DECIMAL, spend_prog_C DECIMAL
Sample data:
+---------------+----------------+--------------+--------------+--------------+
| advertiser_id | advertiser_name| spend_prog_A | spend_prog_B | spend_prog_C |
+---------------+----------------+--------------+--------------+--------------+
| 1 | AlphaCo | 1000 | 500 | NULL |
| 2 | BetaInc | 200 | 1200 | 300 |
| 3 | GammaLLC | 0 | 0 | 0 |
| 4 | DeltaAds | 700 | 800 | 900 |
+---------------+----------------+--------------+--------------+--------------+
Table: program_financials
Columns: program_code CHAR(1), revenue DECIMAL, cost DECIMAL
Sample data:
+--------------+---------+------+
| program_code | revenue | cost |
+--------------+---------+------+
| A | 5000 | 3500 |
| B | 3000 | 2800 |
| C | 2000 | 500 |
+--------------+---------+------+
Mapping: spend_prog_A->'A', spend_prog_B->'B', spend_prog_C->'C'.
Task:
1) Unpivot advertiser_spend into a tall table (advertiser_id, advertiser_name, program_code, spend). Treat NULL spend as 0.
2) Return the single top-spending advertiser overall (exclude advertisers with total_spend = 0). Output columns: advertiser_name, total_spend.
3) Return the program with the highest net_profit = revenue - cost and, separately, the program with the highest profit_margin = CASE WHEN revenue>0 THEN (revenue - cost)/revenue ELSE NULL END. If both metrics select the same program, return only one row; otherwise return two rows with a column reason IN ('highest_net_profit','highest_margin'). Break ties by higher revenue, then alphabetical program_code.
4) Your solution must use the unpivoted rows to compute advertiser totals and a JOIN to program_financials only where needed. Clearly show the final SELECT(s).
Quick Answer: This question evaluates a candidate's ability to perform ANSI SQL data transformations including unpivoting denormalized tables, aggregations, joins, and financial metric calculations such as net profit and profit margin while handling nulls and tie-breaking rules.