Write SQL to compute campaign net revenue
Overview
This question evaluates SQL-based data manipulation and analytics skills, including aggregation, deduplication, date-range filtering, revenue and cost calculations, and selection logic for prioritizing additional donors.
Using the schema and sample data below, write SQL to produce, for each campaign_id and segment, the following metrics for August 2025: total_reached, donors (unique who donated), conversion_rate, gross_donations, variable_cost, fixed_cost, and net_revenue = gross_donations - (fixed_cost + variable_cost). Then, return a one-row summary picking the higher net_revenue between the gala and online campaign. Finally, write a query to suggest the top 10 additional donors (not yet invited to the gala) by last-12-month donation amount to fill remaining gala capacity. Schema:
- donors(donor_id INT PRIMARY KEY, segment CHAR(1))
- campaigns(campaign_id INT PRIMARY KEY, name VARCHAR, channel VARCHAR, fixed_cost DECIMAL, variable_cost_per_reach DECIMAL)
- contacts(contact_id INT PRIMARY KEY, donor_id INT, campaign_id INT, reached_dt DATE)
- donations(donation_id INT PRIMARY KEY, donor_id INT, campaign_id INT, amount DECIMAL, donation_dt DATE) Sample tables (ASCII, minimal rows): Donors +----------+---------+ | donor_id | segment | +----------+---------+ | 1 | H | | 2 | L | | 3 | L | | 4 | H | | 5 | L | +----------+---------+ Campaigns +-------------+------------+---------+------------+------------------------+ | campaign_id | name | channel | fixed_cost | variable_cost_per_reach| +-------------+------------+---------+------------+------------------------+ | 10 | Gala Fall | gala | 20000 | 100 | | 20 | Email Sept | online | 8000 | 1 | +-------------+------------+---------+------------+------------------------+ Contacts +-----------+----------+-------------+------------+ | contact_id| donor_id | campaign_id | reached_dt | +-----------+----------+-------------+------------+ | 101 | 1 | 10 | 2025-08-15 | | 102 | 2 | 10 | 2025-08-15 | | 103 | 4 | 10 | 2025-08-15 | | 201 | 3 | 20 | 2025-08-31 | | 202 | 5 | 20 | 2025-08-31 | +-----------+----------+-------------+------------+ Donations +-------------+----------+-------------+--------+-------------+ | donation_id | donor_id | campaign_id | amount | donation_dt | +-------------+----------+-------------+--------+-------------+ | 301 | 1 | 10 | 1000 | 2025-08-16 | | 302 | 2 | 10 | 250 | 2025-08-16 | | 401 | 3 | 20 | 50 | 2025-09-01 | | 402 | 5 | 20 | 40 | 2025-09-01 | +-------------+----------+-------------+--------+-------------+ Notes:
- Treat variable_cost for gala as per attendee (i.e., per contact row for channel='gala').
- For the “top 10 additional donors” query, assume gala capacity is 100, campaign_id=10 currently has COUNT(*) contacts < 100, and there exists a historical table donations_hist(donor_id, amount, donation_dt). Exclude donors already contacted for campaign_id=10.