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.