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.