Introduction
- In this article we will solve Ad Campaign ROAS SQL question which is asked by Google as per DataLemur website.
If you don’t know DatLemur then please do visit , it’s one of the best website to practice SQL questions and improve your SQL skills.
Question
- We have been given ad_campaigns table and we have to analyze performance of various advertisement account.
- We need to calculate return on ad spend (ROAS) for each advertiser.
Solution
- At first we need to understand what is ROAS. its basically ad revenue / ad spend.
- In order to calculate ROAS ratio we need to group by our input table with advertiser_id , once we group then we can sum all the ad_spend and ad_revenue.
- Once we have total_ad_spend and total_ad_revenue we can get ROAS by dividing them.
- We will round ROAS to 2 decimal places because it was asked in Question.
- Once we run above query we get the desired result.
Final Solution
- Here is the final solution of the problem.
select advertiser_id, round(cast(sum(revenue)/sum(spend) as numeric),2) as ROAS
from ad_campaigns
group by advertiser_id
order by advertiser_id
Submission
- Our solution get accepted by the platform.
Conclusion
- In this article we solved sql question asked by google. We used simple group by to sum columns in input table.
Before You Leave
- Upskill your SQL skills with analyst builder. (Use discount code: ABNEW20OFF for 20% off).
- Crack SQL Interview With Grokking the SQL Interview [Free Sample Copy]