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):
ad_revenueevent_date
DATE
advertiser_id
BIGINT
destination
VARCHAR -- values:
'SHOP'
or
'WEBSITE'
revenue_usd
NUMERIC -- attributed revenue in USD
ad_conversionsevent_date
DATE
advertiser_id
BIGINT
destination
VARCHAR -- values:
'SHOP'
or
'WEBSITE'
conversions
BIGINT -- attributed conversion count
event_date
,
advertiser_id
,
destination
) per table.
event_date in the data
.
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
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).