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