Interview Question Bank · Aggregate before joining · NULLIF · NULLS LAST
4. ROAS by channel without double counting
MediumMarketingQuestion #172
Concept: Aggregate before joining · NULLIF · NULLS LASTshow
Return on ad spend is revenue / spend. Spend and revenue live in different tables, each with many rows per campaign, so joining both at once pairs every spend row with every revenue row and inflates both sums. Total each table per channel on its own (one CTE each), then join the totals. A channel with no spend gets NULL ROAS via NULLIF, and NULLS LAST keeps it at the bottom of a descending sort.
Example (different data):
WITH cost AS (SELECT region, SUM(amount) AS cost FROM shipments GROUP BY region), sales AS (SELECT region, SUM(total) AS sales FROM invoices GROUP BY region) SELECT c.region, ROUND(s.sales / NULLIF(c.cost, 0), 2) AS sales_per_cost FROM cost c LEFT JOIN sales s ON s.region = c.region ORDER BY sales_per_cost DESC NULLS LAST;
Your task
Larkspur Roasters' CMO is moving budget between channels and wants return on ad spend for each one. Revenue is credited to the campaign in ad_conversions.campaign_id; organic orders (NULL campaign) don't count for any channel.
For every channel in ad_campaigns, return channel, spend (total of ad_daily_stats.spend, 0 if none), revenue (total revenue of conversions credited to the channel's campaigns, 0 if none) and roas (revenue / spend rounded to 2 decimals, NULL when spend is 0). Sort by roas descending with NULLs last, then channel.
Starting Postgres in your browser…
Checking your access…