SQL Quest › SQL Interview Questions › Aggregation & Grouping

Department Roster with GROUP_CONCAT

MediumFreeQuerying BasicsAggregation & GroupingString FunctionsSubqueries & CTEs

Google's People Analytics team wants a weekly department roster: for each department, show headcount, avg_salary (rounded to nearest dollar), and employee_list — every employee's name concatenated alphabetically, separated by ', '. Sort by headcount descending. GROUP_CONCAT is a recurring Google interview ask because string aggregation is the kind of SQL that L5+ candidates are expected to know cold but most forget exists.

Solve it in the browser editor →

Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".

Schema

employees

emp_id	name	department	position	salary	hire_date	manager_id	performance_rating
1	Alice Johnson	Engineering	Senior Developer	95000	2019-03-15	5	4.5
2	Bob Smith	Engineering	Developer	75000	2020-06-01	1	3.8
3	Carol Williams	Marketing	Marketing Manager	85000	2018-09-20	NULL	4.2

Expected output: Engineering | 3 | 85000 | Alice, Bob, Carol

Hint

GROUP_CONCAT(name, ', ') concatenates values — but in this SQLite runner, ORDER BY inside GROUP_CONCAT is a syntax error. Order the rows FIRST in a subquery, then aggregate: SELECT department, ..., GROUP_CONCAT(name, ', ') FROM (SELECT * FROM employees ORDER BY name) GROUP BY department.

Concepts

SELECT GROUP BY GROUP_CONCAT Aggregation Subquery String Aggregation

Practise the topic: SQL practice questions · GROUP BY exercises · String function practice · CTE practice

In these company practice sets

Google · Uber

A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.

Department Roster with GROUP_CONCAT

Schema

Hint

Concepts

In these company practice sets

Related questions