Write SQL to compute campaign net revenue
Company: Capital One
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Quick Answer: 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.