You have a table sales with columns region, salesperson, and amount. How would you write a query to get each region with an array of salespersons who made sales over 1000, sorted alphabetically?
hard📝 Application Q8 of 15
PostgreSQL - Aggregate Functions and GROUP BY
You have a table sales with columns region, salesperson, and amount. How would you write a query to get each region with an array of salespersons who made sales over 1000, sorted alphabetically?
ASELECT region, ARRAY_AGG(salesperson ORDER BY salesperson) FROM sales WHERE amount > 1000 GROUP BY region;
BSELECT region, ARRAY_AGG(DISTINCT salesperson ORDER BY salesperson) FROM sales WHERE amount > 1000 GROUP BY region;
CSELECT region, ARRAY_AGG(salesperson) FROM sales GROUP BY region HAVING amount > 1000 ORDER BY salesperson;
DSELECT region, ARRAY_AGG(salesperson ORDER BY amount) FROM sales WHERE amount > 1000 GROUP BY region;
Step-by-Step Solution
Solution:
Step 1: Filter sales over 1000
Use WHERE amount > 1000 to select only those sales.
Step 2: Aggregate salespersons per region uniquely and sorted
Use ARRAY_AGG with DISTINCT and ORDER BY salesperson inside to get unique sorted names.
Step 3: Group results by region
GROUP BY region groups salespersons per region.
Final Answer:
SELECT region, ARRAY_AGG(DISTINCT salesperson ORDER BY salesperson) FROM sales WHERE amount > 1000 GROUP BY region; -> Option B
Quick Check:
Filter + DISTINCT + ORDER BY inside ARRAY_AGG for unique sorted arrays [OK]
Quick Trick:Use WHERE to filter, DISTINCT and ORDER BY inside ARRAY_AGG for unique sorted arrays [OK]
Common Mistakes:
Using HAVING instead of WHERE for filtering
Omitting DISTINCT to remove duplicates
Ordering by wrong column inside ARRAY_AGG
Master "Aggregate Functions and GROUP BY" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
