Write SQL to compute campaign net revenue

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.

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.

Table donors

column	type	constraints
donor_id	INT	PRIMARY KEY
segment	CHAR(1)	NOT NULL

Sample data:

donor_id	segment
1	H
2	L
3	L
4	H
5	L
6	H
7	L
8	H
9	L
10	H
11	L
12	L
13	H
14	H
15	L

Table campaigns

column	type	constraints
campaign_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
channel	VARCHAR(20)	NOT NULL
fixed_cost	DECIMAL(12,2)	NOT NULL
variable_cost_per_reach	DECIMAL(12,2)	NOT NULL

Sample data:

campaign_id	channel	fixed_cost	name	variable_cost_per_reach
10	gala	20000	Gala Fall	100
20	online	8000	Email Sept	1

Table contacts

column	type	constraints
contact_id	INT	PRIMARY KEY
donor_id	INT	NOT NULL
campaign_id	INT	NOT NULL
reached_dt	DATE	NOT NULL

Sample data:

campaign_id	contact_id	donor_id	reached_dt
10	101	1	2025-08-15
10	102	2	2025-08-15
10	103	4	2025-08-15
20	201	3	2025-08-31
20	202	5	2025-08-31

Table donations

column	type	constraints
donation_id	INT	PRIMARY KEY
donor_id	INT	NOT NULL
campaign_id	INT	NOT NULL
amount	DECIMAL(12,2)	NOT NULL
donation_dt	DATE	NOT NULL

Sample data:

amount	campaign_id	donation_dt	donation_id	donor_id
1000	10	2025-08-16	301	1
250	10	2025-08-16	302	2
50	20	2025-09-01	401	3
40	20	2025-09-01	402	5

Expected output

One row per (campaign_id, segment) reached in August 2025 with counts, sums, costs (including fixed-cost allocation), and net revenue.

campaign_id	conversion_rate	donors	fixed_cost	gross_donations	net_revenue	segment	total_reached	variable_cost
10	0.5	1	13333.33	1000	-12533.33	H	2	200
10	1	1	6666.67	250	-6516.67	L	1	100
20	0	0	8000	0	-8002	L	2	2

Hints

1. Aggregate reaches (contacts) by campaign_id and donor segment for 2025-08-01..2025-08-31.
2. To ensure donors/gross_donations are from reached donors, join donations to contacts on (donor_id, campaign_id) and filter donation_dt to August.
3. Allocate fixed_cost proportionally by segment_reached / campaign_reached using a window SUM() over campaign_id.

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.

Table donors

column	type	constraints
donor_id	INT	PRIMARY KEY
segment	CHAR(1)	NOT NULL

Sample data:

donor_id	segment
1	H
2	L
3	L
4	H
5	L
6	H
7	L
8	H
9	L
10	H
11	L
12	L
13	H
14	H
15	L

Table campaigns

column	type	constraints
campaign_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
channel	VARCHAR(20)	NOT NULL
fixed_cost	DECIMAL(12,2)	NOT NULL
variable_cost_per_reach	DECIMAL(12,2)	NOT NULL

Sample data:

campaign_id	channel	fixed_cost	name	variable_cost_per_reach
10	gala	20000	Gala Fall	100
20	online	8000	Email Sept	1

Table contacts

column	type	constraints
contact_id	INT	PRIMARY KEY
donor_id	INT	NOT NULL
campaign_id	INT	NOT NULL
reached_dt	DATE	NOT NULL

Sample data:

campaign_id	contact_id	donor_id	reached_dt
10	101	1	2025-08-15
10	102	2	2025-08-15
10	103	4	2025-08-15
20	201	3	2025-08-31
20	202	5	2025-08-31

Table donations

column	type	constraints
donation_id	INT	PRIMARY KEY
donor_id	INT	NOT NULL
campaign_id	INT	NOT NULL
amount	DECIMAL(12,2)	NOT NULL
donation_dt	DATE	NOT NULL

Sample data:

amount	campaign_id	donation_dt	donation_id	donor_id
1000	10	2025-08-16	301	1
250	10	2025-08-16	302	2
50	20	2025-09-01	401	3
40	20	2025-09-01	402	5

Expected output

A single row: the campaign (among the two given campaigns) with the larger net_revenue for August 2025.

campaign_id	channel	name	net_revenue
20	online	Email Sept	-8002

Hints

1. Compute August reaches and August donations-from-reached at the campaign level.
2. Net revenue is gross_donations minus fixed_cost and variable_cost.
3. Use ORDER BY net_revenue DESC and take 1 row.

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

Table donors

column	type	constraints
donor_id	INT	PRIMARY KEY
segment	CHAR(1)	NOT NULL

Sample data:

donor_id	segment
1	H
2	L
3	L
4	H
5	L
6	H
7	L
8	H
9	L
10	H
11	L
12	L
13	H
14	H
15	L

Table contacts

column	type	constraints
contact_id	INT	PRIMARY KEY
donor_id	INT	NOT NULL
campaign_id	INT	NOT NULL
reached_dt	DATE	NOT NULL

Sample data:

campaign_id	contact_id	donor_id	reached_dt
10	101	1	2025-08-15
10	102	2	2025-08-15
10	103	4	2025-08-15
20	201	3	2025-08-31
20	202	5	2025-08-31

Table donations_hist

column	type	constraints
donation_hist_id	INT	PRIMARY KEY
donor_id	INT	NOT NULL
amount	DECIMAL(12,2)	NOT NULL
donation_dt	DATE	NOT NULL

Sample data:

amount	donation_dt	donation_hist_id	donor_id
1200	2025-05-20	1	15
800	2024-12-15	2	15
1500	2025-02-01	3	8
600	2024-07-10	4	13
500	2025-04-18	5	13
1000	2024-11-03	6	6
900	2025-01-22	7	10
800	2024-08-09	8	14
500	2025-03-10	9	3
200	2024-09-05	10	3
300	2025-05-01	11	7
300	2024-10-20	12	7
100	2024-06-10	13	7
400	2025-02-14	14	11
250	2024-07-25	15	9
50	2025-01-05	16	5
75	2024-06-15	17	5
50	2025-03-02	18	12
1200	2025-03-01	19	2
900	2025-02-20	20	1
700	2025-04-01	21	4
500	2024-05-30	22	6

Expected output

Top 10 donors (not already contacted for campaign_id=10) by total donation amount in 2024-06-02..2025-06-01, including segment and total_last_12mo.

donor_id	segment	total_last_12mo
15	L	2000
8	H	1500
13	H	1100
6	H	1000
10	H	900
14	H	800
3	L	700
7	L	700
11	L	400
9	L	250

Hints

1. Build a list of donors already invited to campaign_id=10 and exclude them.
2. Sum donations_hist.amount over 2024-06-02..2025-06-01 and rank by the total.
3. Use ORDER BY total DESC and LIMIT/FETCH FIRST 10.