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:
Assumptions:
Write SQL for both tasks below: