Katkat
Back

Campaign Efficiency Metrics and Below-Average CTR

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

Last updated

Practice a data scientist SQL interview question that calculates campaign cost and funnel rates, handles zero denominators, and filters campaigns against an average CTR benchmark. The exercise tests PostgreSQL date arithmetic, CTE organization, NULL-safe calculations, and deterministic output.

Campaign Efficiency Metrics and Below-Average CTR

You are given an ad_campaigns table with one row per campaign:

ColumnTypeMeaning
campaign_idINTEGERUnique campaign identifier
campaign_nameVARCHARCampaign name
start_dateDATEFirst active date, inclusive
end_dateDATELast active date, inclusive
daily_budgetDECIMALPlanned budget per active day
impressionsINTEGERRecorded impressions
clicksINTEGERRecorded clicks
conversionsINTEGERRecorded conversions

For this console, use PostgreSQL and the following explicit assumptions: campaign dates are inclusive, each row covers one entire campaign, and daily_budget is constant across its active dates.

Write one query that returns only campaigns whose campaign-level click-through rate is below the arithmetic mean of the valid campaign-level click-through rates.

Return these columns:

  • campaign_id
  • campaign_name
  • total_cost: daily_budget multiplied by the inclusive number of active dates
  • ctr: clicks / impressions
  • conversion_rate: conversions / clicks
  • cost_per_conversion: total_cost / conversions

Use decimal division. When a denominator is zero, return NULL for that rate rather than raising an error. Campaigns with zero impressions do not contribute to the average CTR and cannot be classified as below it. Order the final rows by campaign_id.

After writing the query, be prepared to explain whether daily_budget × active_days represents actual spend or only planned budget.