SQL Queries

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.