String aggregation with STRING_AGG in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this STRING_AGG query?

Consider a table fruits with a column name containing values: 'apple', 'banana', 'cherry'. What is the result of this query?

SELECT STRING_AGG(name, ', ') AS fruit_list FROM fruits;

PostgreSQL

SELECT STRING_AGG(name, ', ') AS fruit_list FROM fruits;

A 'apple, banana, cherry'

B 'apple banana cherry'

C 'apple; banana; cherry'

D 'apple|banana|cherry'

💡 Hint

Look at the separator used in STRING_AGG function.

STRING_AGG concatenates strings from multiple rows using the specified separator. Here, ', ' is used, so the fruits are joined with comma and space.

❓ query_result

intermediate

How does STRING_AGG handle NULL values?

Given a table colors with values: 'red', NULL, 'blue', what is the output of:

SELECT STRING_AGG(color, ', ') AS color_list FROM colors;

PostgreSQL

SELECT STRING_AGG(color, ', ') AS color_list FROM colors;

A 'red, , blue'

B 'red, blue'

C 'redblue'

D NULL

💡 Hint

STRING_AGG ignores NULL values by default.

STRING_AGG skips NULL values and concatenates only non-null strings with the separator.

📝 Syntax

advanced

Which query correctly orders values in STRING_AGG?

You want to concatenate city names from table locations ordered alphabetically. Which query is correct?

A SELECT STRING_AGG(city, ', ') FROM locations ORDER BY city;

B SELECT STRING_AGG(city ORDER BY city, ', ') FROM locations;

C SELECT STRING_AGG(city, ', ') ORDER BY city FROM locations;

D SELECT STRING_AGG(city, ', ' ORDER BY city) FROM locations;

💡 Hint

STRING_AGG supports ORDER BY inside its parentheses.

The ORDER BY clause must be inside STRING_AGG parentheses to order the concatenated values.

❓ optimization

advanced

Optimizing STRING_AGG with DISTINCT

You want to concatenate unique tags from a table posts. Which query efficiently returns distinct tags concatenated with ', '?

A SELECT STRING_AGG(DISTINCT tag, ', ') FROM posts;

B SELECT STRING_AGG(tag, ', ') FROM (SELECT DISTINCT tag FROM posts) AS unique_tags;

C SELECT STRING_AGG(tag, ', ') FROM posts WHERE tag IS DISTINCT;

D SELECT DISTINCT STRING_AGG(tag, ', ') FROM posts;

💡 Hint

STRING_AGG supports DISTINCT inside its first argument.

Using DISTINCT inside STRING_AGG removes duplicates efficiently without subqueries.

🧠 Conceptual

expert

Why use STRING_AGG over array_agg + array_to_string?

Which is the main advantage of using STRING_AGG over combining ARRAY_AGG and ARRAY_TO_STRING for string concatenation in PostgreSQL?

A STRING_AGG allows ordering inside the function, ARRAY_AGG does not.

B STRING_AGG automatically removes duplicates, ARRAY_AGG does not.

C STRING_AGG is faster because it avoids creating intermediate arrays.

D STRING_AGG supports multiple separators, ARRAY_AGG supports only one.

💡 Hint

Think about performance and intermediate data structures.

STRING_AGG concatenates strings directly, avoiding overhead of building arrays first, making it more efficient.

Practice

1. What does the STRING_AGG function do in PostgreSQL?

easy

A. It combines multiple text values into one string with a separator.

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

C. It sorts rows based on a column.

D. It deletes duplicate rows from a table.

Solution

1. Step 1: Understand STRING_AGG purpose

   STRING_AGG is used to join multiple text values into a single string with a separator.

2. Step 2: Compare with other options

   Counting rows, sorting, or deleting duplicates are different functions, not STRING_AGG.

3. Final Answer:

   It combines multiple text values into one string with a separator. -> Option A

4. Quick Check:

   STRING_AGG joins text values = It combines multiple text values into one string with a separator. [OK]

Hint: Remember: STRING_AGG joins strings with a separator [OK]

Common Mistakes:

• Confusing STRING_AGG with COUNT or ORDER BY
• Thinking STRING_AGG sorts rows
• Assuming STRING_AGG removes duplicates

2. Which of the following is the correct syntax to aggregate names separated by commas using STRING_AGG in PostgreSQL?

easy

A. SELECT STRING_AGG(name, ',') FROM users ORDER BY name;

B. SELECT STRING_AGG(name) FROM users;

C. SELECT STRING_AGG(name, ',') ORDER BY name FROM users;

D. SELECT STRING_AGG(name, ',') FROM users;

Solution

1. Step 1: Check basic STRING_AGG syntax

   STRING_AGG requires two arguments: the column and the separator, e.g., STRING_AGG(name, ',').

2. Step 2: Validate query structure

   ORDER BY inside STRING_AGG needs to be inside parentheses, not outside. SELECT STRING_AGG(name, ',') FROM users; is correct syntax without ordering.

3. Final Answer:

   SELECT STRING_AGG(name, ',') FROM users; -> Option D

4. Quick Check:

   Correct STRING_AGG syntax = SELECT STRING_AGG(name, ',') FROM users; [OK]

Hint: STRING_AGG needs column and separator inside parentheses [OK]

Common Mistakes:

• Omitting separator argument
• Placing ORDER BY outside STRING_AGG
• Using STRING_AGG without parentheses

3. Given the table fruits with rows: ('apple'), ('banana'), ('cherry'), what is the result of this query?

SELECT STRING_AGG(name, '; ' ORDER BY name) FROM fruits;

medium

A. 'apple; banana; cherry'

B. 'banana; apple; cherry'

C. 'cherry; banana; apple'

D. 'apple, banana, cherry'

Solution

1. Step 1: Understand ORDER BY inside STRING_AGG

   The query orders names alphabetically: apple, banana, cherry.

2. Step 2: Check separator and output

   Separator is '; ', so the result joins names with '; ' in order.

3. Final Answer:

   'apple; banana; cherry' -> Option A

4. Quick Check:

   Ordered join with '; ' separator = 'apple; banana; cherry' [OK]

Hint: ORDER BY inside STRING_AGG sorts before joining [OK]

Common Mistakes:

• Ignoring ORDER BY inside STRING_AGG
• Using wrong separator
• Assuming default comma separator

4. Identify the error in this query:

SELECT STRING_AGG(name, ', ' ORDER BY name) FROM users ORDER BY name;

medium

A. ORDER BY cannot be used inside STRING_AGG.

B. The query is correct and will run without errors.

C. ORDER BY must be inside STRING_AGG parentheses, not outside.

D. ORDER BY inside STRING_AGG must come after separator argument.

Solution

1. Step 1: Check ORDER BY usage inside STRING_AGG

   ORDER BY inside STRING_AGG after the separator is valid syntax.

2. Step 2: Check outer ORDER BY

   Without GROUP BY, the outer ORDER BY name causes an error because 'name' must appear in GROUP BY or be used in an aggregate function.

3. Final Answer:

   ORDER BY must be inside STRING_AGG parentheses, not outside. -> Option C

4. Quick Check:

   Outer ORDER BY invalid without GROUP BY = ORDER BY must be inside STRING_AGG parentheses, not outside. [OK]

Hint: ORDER BY inside STRING_AGG works; outside requires GROUP BY in aggregate queries [OK]

Common Mistakes:

• Thinking ORDER BY inside STRING_AGG is invalid
• Confusing outer ORDER BY with inner
• Misplacing ORDER BY arguments

5. You have a table orders with columns customer_id and product. How do you write a query to list each customer with a semicolon-separated list of their products sorted alphabetically?

hard

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

B. SELECT customer_id, STRING_AGG(product, '; ' ORDER BY product) FROM orders GROUP BY customer_id;

C. SELECT customer_id, STRING_AGG(product ORDER BY product, '; ') FROM orders GROUP BY customer_id;

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

Solution

1. Step 1: Group by customer_id

   We need to group rows by customer_id to aggregate products per customer.

2. Step 2: Use STRING_AGG with ORDER BY inside

   STRING_AGG(product, '; ' ORDER BY product) joins products with '; ' separator sorted alphabetically.

3. Step 3: Validate options

   SELECT customer_id, STRING_AGG(product, '; ' ORDER BY product) FROM orders GROUP BY customer_id; correctly uses GROUP BY and STRING_AGG with ORDER BY inside parentheses.

4. Final Answer:

   SELECT customer_id, STRING_AGG(product, '; ' ORDER BY product) FROM orders GROUP BY customer_id; -> Option B

5. Quick Check:

   Group by customer and ordered STRING_AGG = SELECT customer_id, STRING_AGG(product, '; ' ORDER BY product) FROM orders GROUP BY customer_id; [OK]

Hint: Put ORDER BY inside STRING_AGG parentheses for sorting [OK]

Common Mistakes:

• Placing ORDER BY outside STRING_AGG
• Omitting GROUP BY when aggregating
• Swapping arguments inside STRING_AGG