Compute SHOP spend share and model performance

You work on ads measurement. Advertisers can drive users to either **Facebook Shop** (`'SHOP'`) or their own **website** (`'WEBSITE'`). After an ad is shown, you attribute downstream revenue and conversions to the destination. Assume the following tables (all timestamps are in **UTC** and `event_date` is a calendar date): ### Table 1: `ad_revenue` - `event_date` DATE - `advertiser_id` BIGINT - `destination` VARCHAR -- values: `'SHOP'` or `'WEBSITE'` - `revenue_usd` NUMERIC -- attributed revenue in USD ### Table 2: `ad_conversions` - `event_date` DATE - `advertiser_id` BIGINT - `destination` VARCHAR -- values: `'SHOP'` or `'WEBSITE'` - `conversions` BIGINT -- attributed conversion count #### Notes/assumptions - There is at most one row per (`event_date`, `advertiser_id`, `destination`) per table. - “Past 30 days” means the **most recent 30 calendar days including the max `event_date` in the data**. ## Tasks 1) **SHOP share over past 30 days** Write SQL to compute, for each `event_date` in the past 30 days, the share of revenue going to `destination='SHOP'`: Required output columns: - `event_date` - `shop_revenue` - `total_revenue` - `shop_revenue_share` = `shop_revenue / total_revenue` 2) **How does the FB model perform?** Using only these two tables, write SQL to produce a stakeholder-ready daily time series for the past 30 days that helps evaluate performance by destination. At minimum, include: - `event_date` - `destination` - `revenue_usd` - `conversions` - `revenue_per_conversion` = `revenue_usd / NULLIF(conversions, 0)` Also include at least one “share”-style diagnostic (e.g., revenue share or conversion share across destinations) that could indicate whether performance is shifting toward SHOP vs WEBSITE. State any additional assumptions you make (e.g., how to handle missing rows / zero totals).