SQLPad Forum question: I did not understand why my solution is incor…

I did not understand why my solution is incorrect.

Hello,

I submit the answer below for Q100. Can you explain why my query is incorrect?

Best Regards

Esin

WITH revenue_table AS

(

SELECT

A.advertiser_id,

SUM(I.spend) AS total_rev,

SUM(A.cost) AS ad_spend

FROM advertiser AS A

LEFT JOIN ad_info AS I

ON I.ad_id = A.ad_id

GROUP BY A.advertiser_id

)

SELECT

advertiser_id,

COALESCE(total_rev, 0) / ad_spend AS roi

FROM revenue_table

Answers

Comment

Hi Esin,

Thanks for your question. You code is definitely on the right direction, however, because of the structure of the two tables, when you join them together, the cost got inflated, so you will have to compute the cost and revenue separatedly in two steps.

If it is still confusing, try write a query and join the 2 tables advertiser and ad_info, take a look at the results, you will see many duplicate rows of cost.

Hope that makes sense.

-Leon

Leon (949)

April 7, 2024, 4:34 p.m.