- 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.
- 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.
- 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.
- 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
- Our solution get accepted by the platform.
- In this article we solved sql question asked by google. We used simple group by to sum columns in input table.