Ad Campaign ROAS — SQL Questions Asked By Google

Solving Questions Asked By Google

Suraj Mishra

·

Follow

2 min read

·

Sep 24, 2022

--

Share

Originally Published in https://asyncq.com/

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.