Query of the Week — AMC SQL explained in context

1 · Business question

How much do two ad audiences overlap (e.g. DSP audience vs. Sponsored Brands audience)? Where are we burning budget on double delivery?

2 · Query (SQL)

SELECT
  COUNT(DISTINCT a.user_id) AS in_a,
  COUNT(DISTINCT b.user_id) AS in_b,
  COUNT(DISTINCT CASE WHEN a.user_id = b.user_id THEN a.user_id END) AS overlap_users,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN a.user_id = b.user_id THEN a.user_id END)
        / NULLIF(COUNT(DISTINCT a.user_id), 0), 2) AS overlap_pct
FROM (SELECT user_id FROM amazon_audiences WHERE audience_id = 'A') a
FULL OUTER JOIN (SELECT user_id FROM amazon_audiences WHERE audience_id = 'B') b
  ON a.user_id = b.user_id;

3 · Interpretation

Overlap above 30% signals cannibalization: re-budget the sets against each other or dedupe via a suppression audience. Low overlap justifies running both channels in parallel.