Katkat
Back

Find top advertisers and cost per KPI

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

Last updated

This question evaluates proficiency in data manipulation and analytical SQL/Python skills, including aggregation, ranking with ties, dataset joins at a defined grain, date-based filtering relative to campaign launch dates, and handling division-by-zero scenarios when computing cost metrics.

You are given three advertising campaign tables.

Table: campaign_spend

ColumnTypeDescription
dateDATECalendar date in UTC.
advertiser_idBIGINTAdvertiser identifier.
campaign_idBIGINTCampaign identifier.
creative_groupVARCHARCreative group within a campaign.
ad_spendDECIMALAmount spent on ads for the row.
impressionsBIGINTNumber of ad impressions.

Table: campaign_conversion

ColumnTypeDescription
dateDATECalendar date in UTC.
advertiser_idBIGINTAdvertiser identifier.
campaign_idBIGINTCampaign identifier.
creative_groupVARCHARCreative group within a campaign.
installsBIGINTNumber of installs attributed to the campaign.
kpi_cntBIGINTFinal KPI conversion count. Different campaigns may optimize for different KPIs, but this column stores the final conversion count for that campaign's KPI.

Table: campaign_goal

ColumnTypeDescription
campaign_idBIGINTCampaign identifier.
campaign_goalVARCHARCampaign optimization goal, such as install, purchase, signup, or other KPI type.
campaign_launch_dateDATECampaign launch date in UTC.

Assume campaign_id maps to exactly one campaign_goal. Spend and conversion rows can be joined at the grain of date, advertiser_id, campaign_id, and creative_group when both spend and conversion information are needed.

Answer the following SQL questions:

  1. Find the top 3 spending advertisers for each day.
  • Aggregate spend at the date and advertiser_id level.
  • Rank advertisers by total daily spend in descending order.
  • Return all advertisers tied within the top 3 ranks if ties occur.
  • Required output columns: date, advertiser_id, daily_ad_spend, spend_rank.
  1. Calculate cost per KPI for each campaign goal.
  • For each campaign_goal, aggregate total ad spend and total KPI conversions across all campaigns with that goal.
  • Use only spend and conversions on or after each campaign's campaign_launch_date.
  • Define cost_per_kpi = total_ad_spend / total_kpi_cnt.
  • If total_kpi_cnt is zero, return NULL for cost_per_kpi instead of dividing by zero.
  • Required output columns: campaign_goal, total_ad_spend, total_kpi_cnt, cost_per_kpi.