Calculate CTR and ad revenue

This interview included two SQL tasks. **Task 1: Compare CTR during peak and non-peak hours** You are given three tables: - `ads(ad_id BIGINT, advertiser_id BIGINT, ad_type STRING, created_at TIMESTAMP)` - `impressions(impression_id BIGINT, ad_id BIGINT, user_id BIGINT, impression_time TIMESTAMP)` - `conversions(conversion_id BIGINT, impression_id BIGINT, click_time TIMESTAMP, conversion_time TIMESTAMP, revenue_usd NUMERIC)` Assumptions: - Each row in `impressions` represents one served ad impression. - A click is represented by a non-null `click_time` associated with the `impression_id`. - There is at most one click record per impression. - All timestamps are in UTC. - Analyze the most recent 30 complete days. - Peak hours are `18:00:00` through `21:59:59` UTC; all other times are non-peak. - `CTR = clicked_impressions / total_impressions`. Write a SQL query that returns: - `time_bucket` (`peak_hour` or `non_peak_hour`) - `impressions` - `clicks` - `ctr` - `ctr_difference_vs_non_peak` **Task 2: Compute US and global ad revenue in USD** You are given two tables: - `ad_revenue(revenue_date DATE, country STRING, currency_code STRING, revenue_amount NUMERIC)` - `exchange_rates(rate_date DATE, currency_code STRING, usd_per_currency NUMERIC)` Assumptions: - `revenue_amount` is stored in the local currency identified by `currency_code`. - `usd_per_currency` is the USD value of one unit of that currency on the same date. - `country = 'US'` identifies United States revenue. - Global revenue includes US revenue. - If an exchange rate is missing for a given date and currency, exclude that revenue row. Write a SQL query that returns, for each `revenue_date`: - `us_revenue_usd` - `global_revenue_usd`