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.

Practice

1. What does the PostgreSQL function ARRAY_AGG do?

easy

A. It counts the number of rows in a table.

B. It collects multiple row values into a single array.

C. It sorts rows in ascending order.

D. It deletes duplicate rows from a table.

Solution

1. Step 1: Understand the purpose of ARRAY_AGG

   ARRAY_AGG is used to gather multiple values from rows into one array value.

2. Step 2: Compare with other options

   Counting rows uses COUNT(), deleting duplicates or sorting rows uses other clauses like DISTINCT or ORDER BY, not ARRAY_AGG.

3. Final Answer:

   It collects multiple row values into a single array. -> Option B

4. Quick Check:

   ARRAY_AGG = collects rows into array [OK]

Hint: ARRAY_AGG groups rows into one array [OK]

Common Mistakes:

• Confusing ARRAY_AGG with COUNT or ORDER BY
• Thinking ARRAY_AGG sorts data by default
• Assuming ARRAY_AGG removes duplicates automatically

2. Which of the following is the correct syntax to aggregate values into an array using ARRAY_AGG in PostgreSQL?

easy

A. SELECT ARRAY_AGG(column_name) FROM table_name;

B. SELECT ARRAY_AGG column_name FROM table_name;

C. SELECT ARRAY_AGG(column_name) table_name;

D. SELECT ARRAY_AGG(column_name) FROM table_name WHERE;

Solution

1. Step 1: Recall correct function call syntax

   ARRAY_AGG requires parentheses around the column name and a FROM clause.

2. Step 2: Check each option

   SELECT ARRAY_AGG(column_name) FROM table_name; uses correct syntax with parentheses and FROM clause; others miss parentheses or have syntax errors.

3. Final Answer:

   SELECT ARRAY_AGG(column_name) FROM table_name; -> Option A

4. Quick Check:

   Correct ARRAY_AGG syntax uses parentheses and FROM [OK]

Hint: Always use parentheses with ARRAY_AGG(column) [OK]

Common Mistakes:

• Omitting parentheses around column_name
• Leaving incomplete WHERE clause
• Missing FROM keyword

3. Given the table students with columns class and name, what is the output of this query?

SELECT class, ARRAY_AGG(name ORDER BY name) FROM students GROUP BY class ORDER BY class;

medium

A. [{class: '1A', array_agg: ['Alice']}, {class: '2B', array_agg: ['Charlie', 'David', 'Bob']}]

B. [{class: '1A', array_agg: ['Bob', 'Alice']}, {class: '2B', array_agg: ['David', 'Charlie']}]

C. [{class: '1A', array_agg: ['Alice', 'Charlie']}, {class: '2B', array_agg: ['Bob', 'David']}]

D. [{class: '1A', array_agg: ['Alice', 'Bob']}, {class: '2B', array_agg: ['Charlie', 'David']}]

Solution

1. Step 1: Understand GROUP BY and ARRAY_AGG with ORDER BY

   The query groups rows by class and collects names into arrays sorted alphabetically.

2. Step 2: Analyze the ordering inside ARRAY_AGG

   Names inside each class array are sorted ascending, so 'Alice' comes before 'Bob' in '1A', and 'Charlie' before 'David' in '2B'.

3. Final Answer:

   [{class: '1A', array_agg: ['Alice', 'Bob']}, {class: '2B', array_agg: ['Charlie', 'David']}] -> Option D

4. Quick Check:

   ARRAY_AGG with ORDER BY sorts array elements [OK]

Hint: ARRAY_AGG(... ORDER BY ...) sorts array elements [OK]

Common Mistakes:

• Ignoring ORDER BY inside ARRAY_AGG
• Mixing names between groups
• Assuming ARRAY_AGG sorts groups, not elements

4. Identify the error in this query:

SELECT class, ARRAY_AGG(name ORDER BY) FROM students GROUP BY class;

medium

A. Missing column name after ORDER BY inside ARRAY_AGG.

B. GROUP BY clause is not allowed with ARRAY_AGG.

C. ARRAY_AGG cannot be used with ORDER BY.

D. Missing FROM keyword before table name.

Solution

1. Step 1: Check syntax of ORDER BY inside ARRAY_AGG

   ORDER BY inside ARRAY_AGG requires a column name to specify sorting order.

2. Step 2: Validate other parts of the query

   GROUP BY is valid with ARRAY_AGG; FROM clause is present; ORDER BY without column is invalid.

3. Final Answer:

   Missing column name after ORDER BY inside ARRAY_AGG. -> Option A

4. Quick Check:

   ORDER BY needs a column name inside ARRAY_AGG [OK]

Hint: ORDER BY inside ARRAY_AGG needs a column name [OK]

Common Mistakes:

• Leaving ORDER BY empty inside ARRAY_AGG
• Thinking GROUP BY disallows ARRAY_AGG
• Forgetting to specify sorting column

5. You have a table orders with columns customer_id and product. You want to create a query that returns each customer_id with an array of unique products they ordered, sorted alphabetically. Which query achieves this?

hard

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

B. SELECT customer_id, ARRAY_AGG(DISTINCT product) FROM orders GROUP BY customer_id ORDER BY product;

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

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

Solution

1. Step 1: Use ARRAY_AGG with DISTINCT and ORDER BY

   To get unique products sorted, use ARRAY_AGG(DISTINCT product ORDER BY product).

2. Step 2: Group by customer_id to aggregate per customer

   GROUP BY customer_id ensures aggregation per customer; ORDER BY inside ARRAY_AGG sorts products alphabetically.

3. Final Answer:

   SELECT customer_id, ARRAY_AGG(DISTINCT product ORDER BY product) FROM orders GROUP BY customer_id; -> Option C

4. Quick Check:

   Use DISTINCT inside ARRAY_AGG with ORDER BY for unique sorted arrays [OK]

Hint: Use DISTINCT inside ARRAY_AGG with ORDER BY for unique sorted arrays [OK]

Common Mistakes:

• Omitting DISTINCT causes duplicates in array
• Placing ORDER BY outside ARRAY_AGG affects rows, not array elements
• Not grouping by customer_id