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.
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.
Read the full Capital One Data Scientist interview experience this question came from
August 2025 campaign metrics by campaign and donor segment (with fixed-cost allocation)
Using the tables below, write a SQL query to produce, for each (campaign_id, segment) in August 2025 (2025-08-01 to 2025-08-31 inclusive), the following metrics:
- total_reached: number of contact rows reached in August 2025
- donors: number of unique donors who donated in August 2025 AND were reached in August 2025 for that same campaign
- conversion_rate = donors / total_reached
- gross_donations: sum of donation amounts in August 2025 from those reached donors
- variable_cost = total_reached * campaigns.variable_cost_per_reach (treat gala variable cost as per attendee, i.e., per contact row)
- fixed_cost: allocate campaigns.fixed_cost proportionally across segments based on August total_reached within the campaign:
fixed_cost_allocated = campaigns.fixed_cost * (segment_total_reached / campaign_total_reached)
- net_revenue = gross_donations - (fixed_cost + variable_cost)
Return one row per (campaign_id, segment) that has at least one reach in August 2025.
Tables
donors(donor_id INT, segment CHAR(1))
campaigns(campaign_id INT, name VARCHAR(100), channel VARCHAR(20), fixed_cost DECIMAL(12,2), variable_cost_per_reach DECIMAL(12,2))
contacts(contact_id INT, donor_id INT, campaign_id INT, reached_dt DATE)
donations(donation_id INT, donor_id INT, campaign_id INT, amount DECIMAL(12,2), donation_dt DATE)
Hints
- Aggregate reaches (contacts) by campaign_id and donor segment for 2025-08-01..2025-08-31.
- To ensure donors/gross_donations are from reached donors, join donations to contacts on (donor_id, campaign_id) and filter donation_dt to August.
Pick the higher net revenue campaign (gala vs online) for August 2025
Using the same tables, compute campaign-level metrics for August 2025 (2025-08-01 to 2025-08-31 inclusive): total_reached, donors (unique donors who donated in August 2025 AND were reached in August 2025 for that campaign), gross_donations, variable_cost, fixed_cost, and net_revenue = gross_donations - (fixed_cost + variable_cost).
Then return a ONE-row result that contains the campaign with the higher net_revenue among the two campaigns in the sample data (the gala campaign and the online campaign). Include: campaign_id, name, channel, net_revenue.
Tables
donors(donor_id INT, segment CHAR(1))
campaigns(campaign_id INT, name VARCHAR(100), channel VARCHAR(20), fixed_cost DECIMAL(12,2), variable_cost_per_reach DECIMAL(12,2))
contacts(contact_id INT, donor_id INT, campaign_id INT, reached_dt DATE)
donations(donation_id INT, donor_id INT, campaign_id INT, amount DECIMAL(12,2), donation_dt DATE)
Hints
- Compute August reaches and August donations-from-reached at the campaign level.
- Net revenue is gross_donations minus fixed_cost and variable_cost.
Suggest top 10 additional gala invitees by last-12-month donation amount
Assume today is 2025-06-01. The gala campaign is campaign_id = 10 and has a capacity of 100 total contacts.
Using donors, contacts, and donations_hist, write a SQL query to suggest the top 10 additional donors to invite to the gala, ranked by their total donation amount in the last 12 months (FROM 2024-06-02 TO 2025-06-01 inclusive).
Rules:
- Exclude any donor who already has a contact row for campaign_id = 10 (invited already, any date).
- Only consider donation history within 2024-06-02..2025-06-01.
- Return donor_id, segment, and total_last_12mo.
- Order by total_last_12mo DESC, then donor_id ASC.
- (You may assume current gala contacts < 100, so there is remaining capacity; still return at most 10 rows.)
Tables
donors(donor_id INT, segment CHAR(1))
contacts(contact_id INT, donor_id INT, campaign_id INT, reached_dt DATE)
donations_hist(donation_hist_id INT, donor_id INT, amount DECIMAL(12,2), donation_dt DATE)
Hints
- Build a list of donors already invited to campaign_id=10 and exclude them.
- Sum donations_hist.amount over 2024-06-02..2025-06-01 and rank by the total.