Katkat
Back

Write SQL to rank advertisers and profitability

InstacartData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.

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).