Write SQL for CTR and Revenue
Quick Overview
This question evaluates proficiency in SQL-based data manipulation and analytics, focusing on joins across event and reference tables, time-of-day bucketing and aggregation for CTR, boolean filtering, and date-based currency conversion for revenue.
You are given the following tables:
ads( ad_id BIGINT, advertiser_id BIGINT, ad_type VARCHAR, -- values include direct and brand advertiser_country CHAR(2), currency_code CHAR(3) )
impressions( impression_id BIGINT, ad_id BIGINT, user_id BIGINT, impression_ts_utc TIMESTAMP, was_clicked BOOLEAN, spend_local NUMERIC(18,2) )
conversions( conversion_id BIGINT, impression_id BIGINT, conversion_ts_utc TIMESTAMP, conversion_value_local NUMERIC(18,2) )
exchange_rates( rate_date DATE, currency_code CHAR(3), usd_per_local NUMERIC(18,6) )
Relationships:
- ads.ad_id = impressions.ad_id
- impressions.impression_id = conversions.impression_id
- exchange_rates joins on DATE(impression_ts_utc) = rate_date and ads.currency_code = currency_code
Assumptions:
- Use the full date range available in the data.
- All timestamps are stored in UTC.
- For the CTR question, define peak hours as 18:00:00-22:59:59 UTC and non-peak hours as all other times.
- CTR = clicked impressions / total impressions, where a clicked impression is one with was_clicked = TRUE.
- Advertising revenue is recognized at impression time and equals impressions.spend_local.
- Convert local-currency revenue to USD using the exchange rate on the impression date.
Write SQL for both tasks below:
- Compare CTR between peak and non-peak hours. Return columns: hour_bucket, impressions, clicks, ctr.
-
Compute daily advertising revenue in USD for:
- U.S. advertisers only, where ads.advertiser_country = 'US'
- all advertisers globally Return columns: report_date, us_revenue_usd, global_revenue_usd.