COLLECT

Syntax

COLLECT(column_name) | COLLECT(DISTINCT column_name)

AMC specifics

• Without DISTINCT duplicates are kept. Null values are considered in both cases.
• COLLECT_SORTED returns the same sorted in one step instead of COLLECT followed by ARRAY_SORT.
• The array suits path analysis, for example the order of ad products per user, inside a CTE.

Example

How many ad products appeared per user. user_id stays in the CTE and is only counted in the output.

WITH paths AS (
  SELECT
    user_id,
    COLLECT(DISTINCT ad_product_type) AS products
  FROM
    sponsored_ads_traffic
  GROUP BY
    1
)
SELECT
  CARDINALITY(products) AS product_types,
  COUNT(DISTINCT user_id) AS users
FROM
  paths
GROUP BY
  1

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