Drill 8 of 12 in Marketing questions
0

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…