SQL Queries

Question

Suppose we have the following schema with two tables: Ads and Events

Ads(ad_id, campaign_id, status)

Events(event_id, ad_id, source, event_type, date, hour)

Each ad can be active or inactive, and this is reflected in the status field.

 

There are different event types:

 

impression (ad is shown to the user)

click (the user clicked on the ad)

conversion (the user installed the app from the advertisement)

We want to write a couple of queries to extract data from these tables. Write SQL queries to extract the following information:

 

 

1) The number of active ads.

 

2) All active campaigns. A campaign is active if there’s at least one active ad.

 

3) The number of active campaigns.

 

  • The number of events per each ad — broken down by event type.
  • CTR(Click through rate) for each ad. CTR=number of impressions/number of clicks
  • CVR(conversion rate) for each ad. CVR=number of clicks/ number of installs.
  • CTR and CVR for each ad broken down by day and hour(most recent first).
  • CTR for each as broken down by source and day.

 

0
Shloka Reddy Lakka 55 years 0 Answers 498 views Great Grand Master 0

Leave an answer

Browse
Browse