Write SQL for CTR and Revenue
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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:
1. Compare CTR between peak and non-peak hours.
Return columns: hour_bucket, impressions, clicks, ctr.
2. 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.
Quick Answer: 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.