Write SQL for CTR and revenue
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Write SQL for the following two tasks.
**Problem 1: CTR during peak vs. non-peak hours**
You are given three tables:
- `ads(ad_id BIGINT, advertiser_id BIGINT, ad_type STRING, status STRING, created_at TIMESTAMP)`
- `impressions(impression_id BIGINT, ad_id BIGINT, user_id BIGINT, impression_ts TIMESTAMP, clicked BOOLEAN)`
- `conversions(conversion_id BIGINT, impression_id BIGINT, conversion_ts TIMESTAMP, conversion_value_usd DECIMAL(18,2))`
Key relationships:
- `ads.ad_id = impressions.ad_id`
- `impressions.impression_id = conversions.impression_id`
Assume all timestamps are stored in UTC. For the last 30 complete days, compare click-through rate during **peak hours** versus **non-peak hours** for active ads. Define:
- peak hours = `18:00:00` to `21:59:59` UTC
- non-peak hours = all other times
- CTR = `clicks / impressions`, where a click is an impression row with `clicked = TRUE`
Return one row per time bucket with these columns:
- `time_bucket` (`'peak'` or `'non_peak'`)
- `impression_count`
- `click_count`
- `ctr`
**Problem 2: US and global ad revenue with FX conversion**
You are given two tables:
- `ad_revenue(event_date DATE, ad_id BIGINT, country_code STRING, currency_code STRING, revenue_local DECIMAL(18,2))`
- `fx_rates(rate_date DATE, currency_code STRING, usd_per_unit DECIMAL(18,6))`
Key relationships:
- `ad_revenue.event_date = fx_rates.rate_date`
- `ad_revenue.currency_code = fx_rates.currency_code`
Assume `usd_per_unit` means 1 unit of local currency equals `usd_per_unit` USD, all dates are UTC calendar dates, and `USD` has a rate of `1.0`. For each date in January 2024, calculate:
- US ad revenue in USD, where `country_code = 'US'`
- global ad revenue in USD, across all countries
Return:
- `event_date`
- `us_revenue_usd`
- `global_revenue_usd`
Overview: This question evaluates SQL-based data manipulation competency, including multi-table joins, aggregations, time-based bucketing and filtering for CTR calculation, and currency conversion using FX rate lookups within the Data Manipulation (SQL/Python) domain.
Two-part SQL task: (1) For the last 30 complete days, compute CTR during peak hours (18:00:00–21:59:59 UTC) vs non-peak for active ads, returning time_bucket, impression_count, click_count, ctr. (2) For each date in January 2024, convert ad revenue to USD using fx_rates and return event_date, us_revenue_usd (country_code = 'US'), and global_revenue_usd (all countries).
Tables
ads(ad_id BIGINT, advertiser_id BIGINT, ad_type VARCHAR, status VARCHAR, created_at TIMESTAMP)
impressions(impression_id BIGINT, ad_id BIGINT, user_id BIGINT, impression_ts TIMESTAMP, clicked BOOLEAN)
conversions(conversion_id BIGINT, impression_id BIGINT, conversion_ts TIMESTAMP, conversion_value_usd DECIMAL(18,2))
ad_revenue(event_date DATE, ad_id BIGINT, country_code VARCHAR(2), currency_code VARCHAR(3), revenue_local DECIMAL(18,2))
fx_rates(rate_date DATE, currency_code VARCHAR(3), usd_per_unit DECIMAL(18,6))
Hints
- For CTR, define peak vs non-peak using EXTRACT(HOUR FROM impression_ts) and group by that bucket.
- Use only active ads by joining impressions to ads and filtering status = 'active'.
Community answers
Answer by SS
WITH filtered_impressions AS (
SELECT
i.impression_id,
i.ad_id,
i.clicked,
i.impression_ts,
EXTRACT(HOUR FROM i.impression_ts) AS hr
FROM impressions i
JOIN ads a
ON i.ad_id = a.ad_id
WHERE i.impression_ts >= CURRENT_DATE - INTERVAL '30 day'
AND i.impression_ts < CURRENT_DATE
),
bucketed AS (
SELECT *,
CASE
WHEN hr BETWEEN 18 AND 21 THEN 'peak'
ELSE 'non_peak'
END AS time_bucket
FROM filtered_impressions
)
SELECT
time_bucket,
COUNT(*) AS impression_count,
SUM(CASE WHEN clicked THEN 1 ELSE 0 END) AS click_count,
SUM(CASE WHEN clicked THEN 1 ELSE 0 END) * 1.0
/ NULLIF(COUNT(*), 0) AS ctr
FROM bucketed
GROUP BY time_bucket;