Ad Campaign ROAS — SQL Questions Asked By Google

  • Post last modified:February 4, 2024
  • Reading time:2 mins read
Photo by Caspar Camille Rubin on Unsplash

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

Leave a Reply