Posted by Shiva, June 6, 2023, 8:14 a.m.
Hi Leon,
For the Q100, I used this solution but 2/5 result rows have the wrong ROI value, can you explain what is wrong in this query ?
SELECT advertiser_id,COALESCE(SUM(spend),0)/ SUM(cost) as roi
FROM ad_info ad
RIGHT JOIN advertiser adv
ON ad.ad_id = adv.ad_id
GROUP BY 1
Hi Shiva,
Good question!
Your solution didn't work because when joining the two tables together, the ad cost got inflated, so we have to compute the cost separately with CTE.
If you join the two tables together and see a few rows samples, you will see what I meant.
For example:
SELECT advertiser_id, ad.ad_id, spend, cost
FROM ad_info ad
RIGHT JOIN advertiser adv
ON ad.ad_id = adv.ad_id
| advertiser_id | ad_id | spend | cost |
|---|---|---|---|
| 1 | 2001 | 9.9 | 12 |
| 1 | 2002 | 0.9 | 9 |
| 1 | 2002 | 29.9 | 9 |
| 2 | 2003 | 29.9 | 19 |
| 2 | 2003 | 9.9 | 19 |
| 2 | 2003 | 0.9 | 19 |
| 1 | 2001 | 2.8 | 12 |
| 1 | 2002 | 9.9 | 9 |
| 1 | 2001 | 9.9 | 12 |
| 2 | 2003 | 1.2 | 19 |
| 1 | 2001 | 1.2 | 12 |
| 1 | 2002 | 3.5 | 9 |
| 2 | 2003 | 6.7 | 19 |
| 3 | 2005 | 9.1 | 9 |
| 4 | 2009 | 8.8 | 57 |
| 5 | null | null | 9 |
Hope it helps,
Leon
