# Analyze Thirty-Day Ad Performance with SQL For this practice version, use the following neutral schema. `clicked` is a Boolean recorded on each impression, and a conversion is attributed to the impression that led to it. ```text ads(ad_id, campaign_id, ad_type) impressions(impression_id, ad_id, user_id, shown_at, clicked) conversions(conversion_id, impression_id, conversion_value, converted_at) ``` Use the inclusive 30-day impression window ending at `:as_of_ts`. Unless you state another choice, attribute conversions by whether their linked impression is in that window. Write SQL and explain the grain of every intermediate result. ### Clarifying Questions to Ask - Can one impression have more than one conversion, and should all values be counted? - Should campaign value per user include exposed users with zero conversions? - What timezone defines a day and “peak hours”? - Is the purpose of the peak-hour analysis descriptive or causal? ### Part 1: Find the five ads with the most impressions Return the top five ads by impression count, with deterministic tie handling. #### What This Part Should Cover - One row per ad after aggregation - The correct 30-day impression filter - Explicit tie-breaking or a stated decision to return all ties ### Part 2: Compute click-through rate by ad type Return impressions, clicks, and click-through rate for each ad type. #### What This Part Should Cover - A click numerator at impression grain - Safe decimal division - Awareness of missing ad metadata and duplicate impressions ### Part 3: Rank campaigns by average conversion value per exposed user Return the top three campaigns when the denominator includes every distinct user exposed to that campaign, including users with zero attributed conversion value. #### What This Part Should Cover - User-campaign aggregation before averaging - Correct handling of multiple impressions and conversions - A clearly defined exposed-user denominator ### Part 4: Evaluate a peak-hour performance hypothesis The team believes ads shown from 18:00 through 21:59 perform better than ads shown at other times. Produce a comparison and describe how you would quantify uncertainty. #### What This Part Should Cover - A predeclared performance metric and timezone - Comparable peak and off-peak aggregates - Recognition of user, campaign, and time-based confounding - An appropriate clustered or experimental inference strategy ### What a Strong Answer Covers - Correct SQL grain and join cardinality across all four tasks - Explicit windows, denominators, and attribution rules - Validation for duplicate or missing data - Statistical restraint in interpreting the peak-hour comparison ### Follow-up Questions 1. How would you include conversions that occur up to seven days after an impression? 2. How would you return all ads tied for fifth place? 3. What changes if users can see several campaigns in one session? 4. How would you design a randomized test of peak-hour delivery rather than rely on historical data?