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`