SQLPad Forum question: #100 shows different sample results than fina…

#100 shows different sample results than final results

Driving myself crazy on this one. The below query produces the same results as those shown in the "sample results."

SELECT
DISTINCT
advertiser_id,
(SUM(spend) OVER (PARTITION BY advertiser_id)
/
SUM(cost) OVER (PARTITION BY advertiser_id)) as roi
FROM advertiser a
LEFT JOIN ad_info ai ON a.ad_id = ai.ad_id
ORDER BY advertiser_id

Sample results:

advertiser_id |        roi
---------------+--------------------
             1 | 0.8095238095238095
             2 |  0.511578947368421


My results:

advertiser_id	roi
1	0.8095238095238095
2	0.511578947368421

Yet these do not match the final results. Is this a bug? The logic of my query seems to track, and it does match the sample, so seems like something is off.

Answers

Comment

Hey Colin,

Thanks for your question, and sorry for the late response. Somehow I missed it. : )

The sample results were there to give you a sense of the results format, not the ground truth.

Sorry if they were misleading, we'll update the sample results.

Your solution looks great, the only issue was that cost were inflated when you calculate it that way, the same spend was computed multiple times after the joining.

If you cauclate the cost directly and compare your results advertiser by advertiser, you will see what I meant:

 SELECT advertiser_id, SUM(cost) AS total_cost
 FROM advertiser
 GROUP BY advertiser_id

Hope it helps, and let me know if you have more questions!

Leon

Leon (949)

Jan. 29, 2023, 10:10 a.m.