Combine multiple JOINs, aggregation, and CASE to build a comprehensive report
I need one final report before the board meeting. For each campaign, show: campaign_name, channel, budget, total sends, open rate (as %), total attributed purchases (positive only), total revenue, and ROI (revenue / budget). Sort by ROI descending. This is the report that decides next quarter's budget allocation.
Each hint you reveal reduces the XP you can earn. Try the query first.
You need data from 3 tables: dim_campaigns, fact_sends, and fact_purchases. Joining all three directly causes row multiplication (fan-out problem)
Build subqueries: one for send metrics (total_sends, open_rate from fact_sends) and one for purchase metrics (purchases, revenue from fact_purchases), then JOIN both to dim_campaigns
ROI = revenue ÷ budget. Use `COALESCE` or `CASE` to handle campaigns with zero revenue. The Birthday Campaign has no purchases but should still appear with 0 ROI