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).
Overview: 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.
Read the full Instacart Data Scientist interview experience this question came from
You are given program-level financials.
Definitions:
- net_profit = revenue - cost
- profit_margin = CASE WHEN revenue > 0 THEN (revenue - cost) / revenue ELSE NULL END
Tasks:
1) Return the program with the highest net_profit.
2) Separately, return the program with the highest profit_margin.
3) If both metrics select the same program, return only one row; otherwise return two rows.
4) Add a column `reason` with values in ('highest_net_profit','highest_margin').
5) Break ties (for each metric) by higher revenue, then alphabetical program_code.
Output columns:
- program_code
- reason
- net_profit
- profit_margin
Tables
advertiser_spend(advertiser_id INT, advertiser_name VARCHAR(100), spend_prog_A DECIMAL(12,2), spend_prog_B DECIMAL(12,2), spend_prog_C DECIMAL(12,2))
program_financials(program_code CHAR(1), revenue DECIMAL(12,2), cost DECIMAL(12,2))
Hints
- Compute net_profit and profit_margin in a single CTE.
- Pick the top row per metric with ORDER BY + FETCH FIRST 1 ROW ONLY.