Array aggregation with ARRAY_AGG in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this ARRAY_AGG query?

Given the table orders with columns customer_id and product, what is the result of this query?

SELECT customer_id, ARRAY_AGG(product ORDER BY product) AS products
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

PostgreSQL

CREATE TABLE orders (customer_id INT, product TEXT);
INSERT INTO orders VALUES
(1, 'apple'), (1, 'banana'), (2, 'carrot'), (2, 'apple'), (3, 'banana');

A [{"customer_id":1,"products":["banana"]},{"customer_id":2,"products":["apple","carrot"]},{"customer_id":3,"products":["banana"]}]

B [{"customer_id":1,"products":["apple","banana"]},{"customer_id":2,"products":["apple","carrot"]},{"customer_id":3,"products":["banana"]}]

C [{"customer_id":1,"products":["apple","banana"]},{"customer_id":2,"products":["carrot"]},{"customer_id":3,"products":["banana"]}]

D [{"customer_id":1,"products":["banana","apple"]},{"customer_id":2,"products":["carrot","apple"]},{"customer_id":3,"products":["banana"]}]

💡 Hint

Remember that ARRAY_AGG collects values into an array, and ORDER BY inside it sorts the array elements.

The query groups rows by customer_id and collects their product values into arrays sorted alphabetically. For customer 1, products are 'apple' and 'banana' sorted as ['apple', 'banana']. For customer 2, 'apple' and 'carrot' sorted as ['apple', 'carrot']. Customer 3 has only 'banana'.

📝 Syntax

intermediate

Which ARRAY_AGG usage is syntactically correct?

Choose the option with correct syntax for using ARRAY_AGG with ordering.

A SELECT ARRAY_AGG(product ORDER BY product) FROM orders;

B SELECT ARRAY_AGG(ORDER BY product) FROM orders;

C SELECT ARRAY_AGG(product, ORDER BY product) FROM orders;

D SELECT ARRAY_AGG(product) ORDER BY product FROM orders;

💡 Hint

Look for the correct placement of ORDER BY inside the ARRAY_AGG function.

Option A correctly places ORDER BY inside the parentheses of ARRAY_AGG. Option A misses the column to aggregate. Option A uses a comma incorrectly. Option A orders the whole query result, not the array elements.

❓ optimization

advanced

How to optimize ARRAY_AGG for distinct values?

You want to aggregate unique product names per customer into an array. Which query is the most efficient?

A SELECT customer_id, ARRAY_AGG(product) FROM (SELECT DISTINCT customer_id, product FROM orders) sub GROUP BY customer_id;

B SELECT customer_id, ARRAY(SELECT DISTINCT product FROM orders WHERE orders.customer_id = o.customer_id) FROM orders o GROUP BY customer_id;

C SELECT customer_id, ARRAY_AGG(DISTINCT product) FROM orders GROUP BY customer_id;

D SELECT customer_id, ARRAY_AGG(product ORDER BY product) FROM orders GROUP BY customer_id;

💡 Hint

PostgreSQL supports DISTINCT inside ARRAY_AGG to remove duplicates efficiently.

Option C uses ARRAY_AGG(DISTINCT ...) which is optimized internally. Option C uses a correlated subquery which is less efficient. Option C deduplicates before aggregation but requires a subquery. Option C does not remove duplicates.

🧠 Conceptual

advanced

What does ARRAY_AGG return when no rows match?

Consider this query with a filter that matches no rows:

SELECT ARRAY_AGG(product) FROM orders WHERE customer_id = 999;

What is the result?

A NULL

B An empty array {}

C An array with one NULL element [NULL]

D An error is raised

💡 Hint

Think about aggregate functions behavior when no rows are aggregated.

Aggregate functions like ARRAY_AGG return NULL when no rows match the filter. They do not return empty arrays or raise errors.

🔧 Debug

expert

Why does this ARRAY_AGG query fail?

Given the query:

SELECT customer_id, ARRAY_AGG(product ORDER BY quantity) FROM orders GROUP BY customer_id;

Assuming quantity is not in the orders table, what error will PostgreSQL raise?

A ERROR: aggregate function calls cannot contain ORDER BY

B No error, query runs successfully

C ERROR: syntax error at or near "ORDER"

D ERROR: column "quantity" does not exist

💡 Hint

Check if all columns used inside ARRAY_AGG exist in the table.

The error occurs because quantity is referenced in the ORDER BY inside ARRAY_AGG but does not exist in the orders table.