Quick Overview

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

Company: ByteDance

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

# Campaign Efficiency Metrics and Below-Average CTR You are given an `ad_campaigns` table with one row per campaign: | Column | Type | Meaning | | --- | --- | --- | | `campaign_id` | INTEGER | Unique campaign identifier | | `campaign_name` | VARCHAR | Campaign name | | `start_date` | DATE | First active date, inclusive | | `end_date` | DATE | Last active date, inclusive | | `daily_budget` | DECIMAL | Planned budget per active day | | `impressions` | INTEGER | Recorded impressions | | `clicks` | INTEGER | Recorded clicks | | `conversions` | INTEGER | Recorded 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.

Overview: 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.

Using PostgreSQL, compute each campaign's total_cost as daily_budget multiplied by the inclusive number of active dates, ctr as clicks / impressions, conversion_rate as conversions / clicks, and cost_per_conversion as total_cost / conversions. Use decimal division and return NULL whenever a rate's denominator is zero. Compute the arithmetic mean over campaign-level CTR values for campaigns with nonzero impressions, then return only campaigns whose CTR is strictly below that mean. Campaigns with zero impressions neither contribute to the mean nor qualify as below it. Return campaign_id, campaign_name, total_cost, ctr, conversion_rate, and cost_per_conversion ordered by campaign_id.

Tables

ad_campaigns(campaign_id INTEGER, campaign_name TEXT, start_date DATE, end_date DATE, daily_budget NUMERIC(12,2), impressions INTEGER, clicks INTEGER, conversions INTEGER)

Hints

  1. Build one campaign-level metrics CTE before calculating the average CTR.
  2. Subtracting PostgreSQL DATE values gives the number of boundaries crossed; add one for inclusive dates.

Loading coding console...