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.
Explain how you would calculate ROI for a marketing campaign using separate spend and revenue tables. Your answer should cover:
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.
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;
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;
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)
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
