# Compute Each Advertiser's Share of Shop Ad Spend You have the following daily advertising table: ```text ads_detail( advertiser_id, ad_id, ad_type, ad_objective, ad_spend, ds ) ``` Write SQL that returns, for every advertiser with at least $10,000 in total ad spend during the seven-day window ending on `:as_of_date`, the fraction of that spend attributable to ads whose `ad_type = 'Shop'`. Treat the window as inclusive: `:as_of_date - 6 days` through `:as_of_date`. Return the advertiser, total spend, Shop spend, and Shop spend share. State how you would handle null or duplicated source rows. ### Clarifying Questions to Ask - Is `ad_spend` already aggregated to one row per ad and day? - Should refunds or negative spend be included? - Is `ad_type` case-sensitive, and can it be null? - Should the $10,000 threshold include or exclude advertisers exactly at the boundary? ### What a Strong Answer Covers - Correct conditional aggregation over one consistent seven-day window - Filtering advertisers after aggregation with `HAVING` - Decimal division and safe handling of a zero denominator - Explicit assumptions about table grain, duplicates, nulls, and negative spend - A result that is easy to validate against component totals ### Follow-up Questions 1. How would the query change if ad type can change during the week? 2. How would you test that duplicate ingestion did not inflate spend? 3. How would you compare the distribution of Shop share across advertiser segments? 4. Which indexes or partitions would matter on a very large daily table?