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.
Leave an answer