-- Aggregate function
ARRAY_AGG(expression) [WITHIN GROUP (ORDER BY ...)] 

-- Scalar function
ARRAY_CONSTRUCT(value1, value2, ...)

-- Aggregate function
ARRAY_AGG(expression [ORDER BY ...] [LIMIT n])

-- Scalar function (literal syntax)
[value1, value2, ...]

-- Aggregate function
COLLECT_LIST(expression)  -- with duplicates
COLLECT_SET(expression)   -- distinct values

-- Scalar function
ARRAY(value1, value2, ...)

SELECT 
    customer_id,
    customer_name,
    ARRAY_AGG(order_id) WITHIN GROUP (ORDER BY order_date) as order_ids,
    ARRAY_AGG(order_total) WITHIN GROUP (ORDER BY order_date) as order_totals,
    COUNT(*) as order_count,
    SUM(order_total) as total_spent
FROM orders
GROUP BY customer_id,

SELECT 
    customer_id,
    customer_name,
    ARRAY_AGG(order_id ORDER BY order_date) as order_ids,
    ARRAY_AGG(order_total ORDER BY order_date) as order_totals,
    COUNT(*) as order_count,
    SUM(order_total) as total_spent
FROM orders
GROUP BY customer_id,

SELECT 
    customer_id,
    customer_name,
    COLLECT_LIST(order_id) as order_ids,
    COLLECT_LIST(order_total) as order_totals,
    COUNT(*) as order_count,
    SUM(order_total) as total_spent
FROM orders
GROUP BY customer_id,

SELECT 
    product_id,
    product_name,
    ARRAY_CONSTRUCT(price, cost, msrp) as price_array,
    ARRAY_CONSTRUCT(category, subcategory, brand) as taxonomy_array,
    ARRAY_CONSTRUCT(
        OBJECT_CONSTRUCT('type', 'price', 'value', price),
        OBJECT_CONSTRUCT('type', 'cost', 'value', cost),
        OBJECT_CONSTRUCT('type', 'msrp', 'value', msrp)
    ) as price_objects
FROM

SELECT 
    product_id,
    product_name,
    [price, cost, msrp] as price_array,
    [category, subcategory, brand] as taxonomy_array,
    [
        STRUCT('price' as type, price as value),
        STRUCT('cost' as type, cost as value),
        STRUCT('msrp' as type, msrp as value)
    ] as price_objects
FROM

SELECT 
    product_id,
    product_name,
    ARRAY(price, cost, msrp) as price_array,
    ARRAY(category, subcategory, brand) as taxonomy_array,
    ARRAY(
        STRUCT('price' as type, price as value),
        STRUCT('cost' as type, cost as value),
        STRUCT('msrp' as type, msrp as value)
    ) as price_objects
FROM

SELECT 
    product_id,
    product_name,
    ARRAY_AGG(DISTINCT customer_id) as unique_customers,
    ARRAY_AGG(DISTINCT region) WITHIN GROUP (ORDER BY region) as regions_sold,
    COUNT(DISTINCT customer_id) as customer_count
FROM sales
GROUP BY product_id,

SELECT 
    product_id,
    product_name,
    ARRAY_AGG(DISTINCT customer_id) as unique_customers,
    ARRAY_AGG(DISTINCT region ORDER BY region) as regions_sold,
    COUNT(DISTINCT customer_id) as customer_count
FROM sales
GROUP BY product_id,

SELECT 
    product_id,
    product_name,
    COLLECT_SET(customer_id) as unique_customers,
    COLLECT_SET(region) as regions_sold,
    COUNT(DISTINCT customer_id) as customer_count
FROM sales
GROUP BY product_id,

WITH order_items AS (
    SELECT 
        order_id,
        ARRAY_AGG(  -- or COLLECT_LIST for Databricks
            STRUCT(
                product_id,
                product_name,
                quantity,
                unit_price,
                quantity * unit_price as line_total
            ) ORDER BY product_id
        ) as items
    FROM order_line_items
    GROUP BY order_id
)
SELECT 
    o.customer_id,
    o.customer_name,
    ARRAY_AGG(
        STRUCT(
            o.order_id,
            o.order_date,
            o.order_total,
            oi.items
        ) ORDER BY o.order_date
    ) as orders
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.customer_id,

SELECT 
    customer_id,
    tags,
    ARRAY_SIZE(tags) as tag_count,
    ARRAY_CONTAINS('VIP'::VARIANT, tags) as is_vip,
    ARRAY_SLICE(tags, 0, 2) as first_two_tags,
    ARRAY_APPEND(tags, 'NEW') as tags_with_new,
    ARRAY_CAT(tags, ARRAY_CONSTRUCT('EXTRA1', 'EXTRA2')) as extended_tags
FROM

SELECT 
    customer_id,
    tags,
    ARRAY_LENGTH(tags) as tag_count,
    'VIP' IN UNNEST(tags) as is_vip,
    ARRAY(SELECT tag FROM UNNEST(tags) tag LIMIT 2) as first_two_tags,
    ARRAY_CONCAT(tags, ['NEW']) as tags_with_new,
    ARRAY_CONCAT(tags, ['EXTRA1', 'EXTRA2']) as extended_tags
FROM

SELECT 
    customer_id,
    tags,
    SIZE(tags) as tag_count,
    ARRAY_CONTAINS(tags, 'VIP') as is_vip,
    SLICE(tags, 1, 2) as first_two_tags,
    ARRAY_APPEND(tags, 'NEW') as tags_with_new,
    CONCAT(tags, ARRAY('EXTRA1', 'EXTRA2')) as extended_tags
FROM

SELECT 
    department,
    LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) as employees_list,
    ARRAY_AGG(employee_name) WITHIN GROUP (ORDER BY employee_name) as employees_array,
    COUNT(*) as employee_count
FROM employees
GROUP BY

SELECT 
    department,
    STRING_AGG(employee_name, ', ' ORDER BY employee_name) as employees_list,
    ARRAY_AGG(employee_name ORDER BY employee_name) as employees_array,
    COUNT(*) as employee_count
FROM employees
GROUP BY

SELECT 
    department,
    CONCAT_WS(', ', COLLECT_LIST(employee_name)) as employees_list,
    COLLECT_LIST(employee_name) as employees_array,
    COUNT(*) as employee_count
FROM employees
GROUP BY