This SQL exercise is a part of a section dedicated for data analysts , providing data analysis tasks that require both business understanding and technical skills.


About this Exercise

In the Online Advertising Market, a merchant who wants to advertise a new product, can contact different publishers (such as Google or Facebook) and pay them for placing an ad (such as a banner, or video) in their platform.
In return, the online advertising platform gives the merchant metrics to help him measure his ad performance :

  • Clicks – Click happens when someone sees the ad and clicks on it, going to the website for more information.
  • Conversions – After that someone landed on the website, conversion happens when an action (you have defined as a converting action) takes place, for example: online purchase or a call to the business from a mobile phone


ERD General Description

The following database simulates data of an online advertising platform.

  • Within the Ads table you’ll find data regarding the various ads
  • Advertisers table lists the different advertisers
  • Clicks reflects the data regarding the clicks on every ad
  • Conversions table lists the clicked conversions





The database creation script can be downloaded using the following link



The following exercises are constructed in ascending difficulty order. First ones are pretty easy, made for warm-up. The advanced exercises, on the other hand, can be quite challenging.

Basic Analysis

  1. List the first 10 ads, sort the results by their names in an ascending order
  2. Display all clicks made in Sweden using Chrome browser
  3. List all conversions made in 2017

Advanced Analysis

  1. Using Clicks table, what is the most frequently used browser ?
  2. Which ad has the highest amount of clicks ? display the distribution of clicks for each country
  3. Conversion rate is calculated using the following formula : SUM(total_conversions) \ SUM(total_clicks) * 100. Find out the conversion rate for the ad with the highest amount of clicks
  4. Display the top-5 ads, having the highest conversion rate
  5. Is there any conversion rate differance between the browsers?
  6. In average, for each ad, how many days it took for a click to become a conversion?
  7. What is the most frequently used browser in Brazil