Write SQL to rank advertisers and profitability

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

Find program with highest net profit and highest profit margin (dedupe if same program)

Table advertiser_spend

column	type	constraints
advertiser_id	INT	PRIMARY KEY, NOT NULL
advertiser_name	VARCHAR(100)	NOT NULL
spend_prog_A	DECIMAL(12,2)
spend_prog_B	DECIMAL(12,2)
spend_prog_C	DECIMAL(12,2)

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

column	type	constraints
program_code	CHAR(1)	PRIMARY KEY, NOT NULL
revenue	DECIMAL(12,2)	NOT NULL
cost	DECIMAL(12,2)	NOT NULL

Sample data:

cost	program_code	revenue
3500	A	5000
2800	B	3000
500	C	2000

Expected output

One or two rows: the top program by net profit and the top program by profit margin. If they are the same program, only one row is returned. Ties are broken by higher revenue, then alphabetical program_code.

net_profit	profit_margin	program_code	reason
1500	0.3	A	highest_net_profit
1500	0.75	C	highest_margin

Hints

1. Compute net_profit and profit_margin in a single CTE.
2. Pick the top row per metric with ORDER BY + FETCH FIRST 1 ROW ONLY.
3. To return only one row when both metrics pick the same program, union the two choices then deduplicate by program_code with a priority.