Why is my query incorrect in 100. Advertiser ROI question?

Hi Eberto,
Thanks for your question.
I saw there were two bugs:
1. the revenue should be summed up to compute the total revenue per advertiser, so instead of using AVG, it should be SUM;
2. with INNER JOIN you missed advertisers with no revenue.
Made some small changes to your solution and it worked.
WITH revenue as (
SELECT ad_id, sum(spend) as total
FROM ad_info
group by ad_id
), cost as (
SELECT advertiser_id, ad_id, sum(cost) as total
FROM advertiser
group by 1,2)
SELECT advertiser_id, SUM(COALESCE(revenue.total, 0))/sum(cost.total)
FROM revenue
RIGHT JOIN cost on revenue.ad_id=cost.ad_id
group by 1
Let us know if you have further questions.
Leon