ARRAY_INTERSECT

Syntax

ARRAY_INTERSECT(array1, array2)

AMC specifics

• The siblings ARRAY_UNION (all elements without duplicates) and ARRAY_COMPLEMENT (elements only in the first array) solve related set tasks.
• Combined with CARDINALITY you can count the size of the intersection.

Example

Users where the same campaign name appears in sponsored ads and in the DSP.

WITH per_user AS (
  SELECT
    user_id,
    COLLECT(DISTINCT campaign) AS paid_campaigns
  FROM
    sponsored_ads_traffic
  GROUP BY
    1
),
dsp AS (
  SELECT
    user_id,
    COLLECT(DISTINCT campaign) AS dsp_campaigns
  FROM
    dsp_impressions
  GROUP BY
    1
)
SELECT
  COUNT(DISTINCT p.user_id) AS users_in_both
FROM
  per_user p
  JOIN dsp d ON p.user_id = d.user_id
WHERE
  CARDINALITY(ARRAY_INTERSECT(p.paid_campaigns, d.dsp_campaigns)) > 0

Checked against the AMC schema, not executed on an AMC instance.