Drill 1 of 12 in Marketing questions
0

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…