Calculate Campaign ROI from Spend

Context

Marketing analysts often need to combine ad spend and attributed revenue to evaluate campaign performance. This is a common SQL interview topic because it tests whether you understand joins, aggregation, null handling, and metric definitions.

Core Question

Explain how you would calculate ROI for a marketing campaign using separate spend and revenue tables. Your answer should cover:

1. How to join the tables at the correct grain
2. How to aggregate spend and revenue before calculating ROI
3. How to handle campaigns with missing revenue or zero spend
4. The standard ROI formula and any assumptions you would state

Scope Guidance

The interviewer is looking for a practical SQL-oriented explanation, not just the formula. You should discuss table grain, why incorrect joins can duplicate values, and how to safely compute ROI in PostgreSQL using COALESCE and CASE WHEN.

Match the Join Grain

Before calculating ROI, make sure both tables are aligned at the same level of detail, such as campaign_id and date or just campaign_id. If one table has daily rows and the other has transaction-level rows, joining them directly can multiply values and produce incorrect totals.

SELECT campaign_id, SUM(spend_amount) AS total_spend
FROM campaign_spend
GROUP BY campaign_id;

Aggregate Before Calculating ROI

ROI should usually be calculated from total revenue and total spend, not row by row. Aggregating first avoids distorted results and reflects the overall campaign return.

SELECT campaign_id,
       SUM(revenue_amount) AS total_revenue,
       SUM(spend_amount) AS total_spend
FROM ...
GROUP BY campaign_id;

Handle NULL Revenue with COALESCE

Some campaigns may have spend but no attributed revenue yet. In those cases, COALESCE converts NULL revenue to 0 so the calculation still works and the campaign remains in the output.

COALESCE(total_revenue, 0)

Protect Against Division by Zero

If spend is zero, the standard ROI formula would divide by zero. Use CASE WHEN total_spend = 0 THEN NULL ELSE ... END to avoid errors and make the edge case explicit.

CASE
  WHEN total_spend = 0 THEN NULL
  ELSE (total_revenue - total_spend) / total_spend
END AS roi