Katkat
Back

Compute SHOP spend share and model performance

MetaData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

This question evaluates proficiency in SQL/Python data manipulation and analytics, focusing on aggregations, joins, time-window computations, handling missing or zero values, and deriving key metrics such as revenue share and revenue-per-conversion.

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
  1. 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).