Concatenate rows into a single string

-- Postgres / SQL Server (2017+) / DuckDB / Snowflake / BigQuery.
SELECT user_id, STRING_AGG(product_name, ', ' ORDER BY product_name) AS products
FROM orders
GROUP BY user_id;

-- Oracle / Snowflake / Redshift.
SELECT user_id, LISTAGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name) AS products
FROM orders
GROUP BY user_id;

-- MySQL / SQLite.
SELECT user_id, GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR ', ') AS products
FROM orders
GROUP BY user_id;

-- Top 3 products per user (use a window function + filter, then aggregate).
WITH user_product_counts AS (
  SELECT o.user_id, p.name AS product_name, COUNT(*) AS order_count
  FROM orders o
  JOIN products p ON p.id = o.product_id
  GROUP BY o.user_id, p.name
),
ranked AS (
  SELECT user_id, product_name,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_count DESC) AS rn
  FROM user_product_counts
)
SELECT user_id,
       STRING_AGG(product_name, ', ' ORDER BY rn) AS top_products
FROM ranked
WHERE rn <= 3
GROUP BY user_id;

-- Distinct + sorted (de-dupe and order).
WITH user_tags(user_id, tag) AS (
  VALUES (1, 'vip'), (1, 'beta'), (1, 'vip'), (2, 'beta')
)
SELECT
  user_id,
  STRING_AGG(DISTINCT tag, ',' ORDER BY tag) AS tags
FROM user_tags
GROUP BY user_id;

-- Build an array instead of a delimited string (often a better contract).
-- (JSON array: JSON_AGG on Postgres, json_group_array on DuckDB.)
SELECT o.user_id, ARRAY_AGG(p.name ORDER BY p.name) AS products_arr
FROM orders o
JOIN products p ON p.id = o.product_id
GROUP BY o.user_id;        -- Postgres / DuckDB