Interview Question Bank · LEFT JOIN with conditions in ON · COUNT(column)
8. Share of conversions that were multi-touch
MediumMarketingQuestion #176
Concept: LEFT JOIN with conditions in ON · COUNT(column)show
To count related rows that meet a condition while keeping parents with none, put the condition in the LEFT JOIN's ON clause and count a column from the joined table: COUNT(t.id) is 0 when nothing matched. The same condition in WHERE throws away the unmatched parents, which quietly turns the LEFT JOIN into an inner join and shrinks your denominator.
Example (different data):
WITH per_ticket AS ( SELECT k.id, k.opened_at, COUNT(r.id) AS replies FROM tickets k LEFT JOIN replies r ON r.ticket_id = k.id AND r.sent_at <= k.closed_at GROUP BY k.id, k.opened_at ) SELECT COUNT(*) FILTER (WHERE replies >= 2) AS back_and_forth, COUNT(*) AS tickets FROM per_ticket;
Your task
Larkspur Roasters' analytics lead is arguing for multi-touch attribution and wants to show how many purchases involved more than one marketing touch. Only touches at or before the purchase count.
For each month of converted_at (YYYY-MM), return month, conversions (all conversions that month, including ones with no touches), multi_touch (conversions with 2 or more touches at or before converted_at) and multi_touch_pct (multi_touch as a % of conversions, rounded to 1 decimal). Sort by month.
Starting Postgres in your browser…
Checking your access…