Katkat
Back

Compute Each Advertiser's Share of Shop Ad Spend

MetaData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

This problem tests your ability to perform windowed aggregations, conditional filtering, and percentage calculations across multiple tables using SQL.

Problem Statement

You are given two tables, ad_spend and shop_spend. You need to calculate the percentage of total Shop ad spend that each advertiser contributes for the most recent 7-day period (inclusive of today), but only for advertisers whose total spend across all ad formats in that period exceeds $1,000.

Tables

  • ad_spend (ad_id, advertiser_id, spend, date)
  • shop_spend (ad_id, spend, date)

Task

Write a SQL query to output the advertiser_id and their share_of_shop_spend (as a decimal between 0 and 1).

  • share_of_shop_spend is defined as: (Advertiser's Shop Spend) / (Total Global Shop Spend).
  • Only include advertisers with a total ad_spend > 1000 in the last 7 days.
  • If an advertiser has no Shop spend, they should not be included in the final output.

Example

Input: ad_spend table shows Advertiser A spent $1200 total, with $300 specifically on 'Shop' ads. Total global 'Shop' spend across all advertisers is $1000.

Output:

advertiser_idshare_of_shop_spend
A0.30

Constraints

  • The 7-day window is determined relative to the maximum date found in the ad_spend table.
  • Handle potential division by zero if global shop spend is zero.
  • Ensure ad_id granularity is handled correctly since one ad_id can appear in both tables.

Hints

Hint 1

First, filter both tables to only include data from the last 7 days based on the max date in the `ad_spend` table.

Hint 2

Use a CTE or subquery to aggregate the total spend per advertiser and identify those above the $1,000 threshold.

Hint 3

Use a second CTE to aggregate the total Shop spend per advertiser and the total global Shop spend for the denominator.

Follow-up questions

  • How would you modify your query if an advertiser has multiple entries for the same ad on the same day due to data logging issues?
  • What if the requirement changed to look at the last 7 days relative to the current date rather than the maximum date in the dataset?
  • How would the query perform if the tables contained billions of rows, and what indexes would you suggest adding to optimize it?