ARRAY_TO_STRING

Syntax

ARRAY_TO_STRING(array, separator_pattern)

AMC specifics

• This makes paths readable in the output, for example "sponsored_brands > sponsored_products", instead of writing an array into the CSV.
• Amazon does not list a function called ARRAY_JOIN in the AMC function list.

Example

Product mix per user as readable text.

WITH paths AS (
  SELECT
    user_id,
    ARRAY_DISTINCT(COLLECT_SORTED(ad_product_type)) AS products
  FROM
    sponsored_ads_traffic
  GROUP BY
    1
)
SELECT
  ARRAY_TO_STRING(products, ' + ') AS product_mix,
  COUNT(DISTINCT user_id) AS users
FROM
  paths
GROUP BY
  1

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