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.

Find Campaigns with Below-Average Click-Through Rate

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.

Table ad_campaigns

column	type	constraints
campaign_id	INTEGER	PRIMARY KEY
campaign_name	TEXT	NOT NULL
start_date	DATE	NOT NULL
end_date	DATE	NOT NULL
daily_budget	NUMERIC(12,2)	NOT NULL
impressions	INTEGER	NOT NULL
clicks	INTEGER	NOT NULL
conversions	INTEGER	NOT NULL

Sample data:

campaign_id	campaign_name	clicks	conversions	daily_budget	end_date	impressions	start_date
1	Awareness	50	5	100	2025-01-10	1000	2025-01-01
2	One Day Launch	100	0	200	2025-01-05	1000	2025-01-05
3	No Delivery	0	0	50	2025-01-03	0	2025-01-01
4	Leap Weekend	40	10	80	2024-03-01	200	2024-02-28
5	Retargeting	20	2	30	2025-05-12	400	2025-05-10

Expected output

campaign_id	campaign_name	total_cost	ctr	conversion_rate	cost_per_conversion
1	Awareness	1000	0.05	0.1	200
5	Retargeting	90	0.05	0.1	45

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.
3. NULLIF protects each denominator, and AVG naturally ignores NULL CTR values.