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📝 Application Q15 of 15
PostgreSQL - Aggregate Functions and GROUP BY
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?
ASELECT customer_id, STRING_AGG(product, '; ') ORDER BY product FROM orders GROUP BY customer_id;
BSELECT customer_id, STRING_AGG(product, '; ' ORDER BY product) FROM orders GROUP BY customer_id;
CSELECT customer_id, STRING_AGG(product ORDER BY product, '; ') FROM orders GROUP BY customer_id;
DSELECT customer_id, STRING_AGG(product, '; ') FROM orders ORDER BY customer_id;
Step-by-Step Solution
Solution:
Step 1: Group by customer_id
We need to group rows by customer_id to aggregate products per customer.
Step 2: Use STRING_AGG with ORDER BY inside
STRING_AGG(product, '; ' ORDER BY product) joins products with '; ' separator sorted alphabetically.
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.
Final Answer:
SELECT customer_id, STRING_AGG(product, '; ' ORDER BY product) FROM orders GROUP BY customer_id; -> Option B
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]
Quick Trick: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
Master "Aggregate Functions and GROUP BY" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
