Interview Question Bank · Ratio of sums · NULLIF for zero denominators
1. CTR and CPC per campaign
EasyMarketingQuestion #169
Concept: Ratio of sums · NULLIF for zero denominatorsshow
Click-through rate is clicks / impressions and cost per click is spend / clicks. Compute both from totals (SUM over SUM), not as an average of daily ratios, which weights a quiet day the same as a busy one. When a denominator can be 0, NULLIF(denominator, 0) turns it into NULL, so the ratio comes out NULL instead of failing with division by zero.
Example (different data):
SELECT store, ROUND(100.0 * SUM(returns) / NULLIF(SUM(orders), 0), 2) AS return_rate_pct, ROUND(SUM(refunded) / NULLIF(SUM(returns), 0), 2) AS avg_refund FROM store_days GROUP BY store;
Your task
Larkspur Roasters, a fictional online coffee subscription, runs ads on search, social and display. The paid media lead wants a one-glance table of how each campaign is doing.
For every campaign with rows in ad_daily_stats, return name, impressions, clicks, spend (all totals), ctr_pct (100 * clicks / impressions, rounded to 2 decimals) and cpc (spend / clicks, rounded to 2 decimals). When a denominator is 0 the ratio should be NULL. Sort by name.
Starting Postgres in your browser…
Checking your access…