JOIN campaigns with purchases and calculate return on investment
I need to know which campaigns are actually worth the money. Can you calculate the ROI for each campaign? I need campaign_name, budget, total revenue (positive purchases only), and a revenue-to-budget ratio. Sort by the ratio — I want to see which ones gave us the best bang for our buck.
Each hint you reveal reduces the XP you can earn. Try the query first.
ROI = revenue ÷ budget. Use a `LEFT JOIN` from `dim_campaigns` to `fact_purchases` so campaigns with zero purchases still appear
For revenue, use conditional aggregation to exclude refunds: `SUM(CASE WHEN p.amount > 0 THEN p.amount ELSE 0 END)`
Calculate the ratio: `ROUND((SUM(...) / c.budget)::numeric, 2) AS roi_ratio` and ORDER BY roi_ratio DESC