Write SQL for CTR and revenue

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