Application of SQL joins, aggregation, ordering, grouping, and limit

Introduction

This question serves as a great introduction to understanding the need for SQL JOINs and how to effectively use them. It also covers essential SQL features such as aggregate functions, ordering, grouping, and limiting the size of the result set.

Question

Write a query to identify the manager with the biggest team size.
You may assume there is only one manager with the largest team size.

Example:

Input

Solution

Since we have manager_id on the employee table, we can count managers by their id using the count() operation. This will give us manager_id, and a number of times it’s being used in the table for employee records.

SELECT e.manager_id , COUNT(e.manager_id) as team_size
FROM employees e
GROUP BY e.manager_id
ORDER BY team_size DESC
LIMIT 1

But the output needs the manager’s name and not the id. So to get the name of the manager, we can join the manager table to the employee table using the join key which is manager_id.

SELECT m.name AS manager, COUNT(m.id) AS team_size FROM employees e 
INNER JOIN managers m 
ON e.manager_id = m.id
GROUP BY m.id
ORDER BY team_size DESC 
LIMIT 1;

Conclusion

SQL JOINs are crucial for combining data from multiple tables to solve complex queries. By integrating JOINs with aggregate functions, grouping, ordering, and limiting, you can perform detailed data analysis and retrieval efficiently.

Resources

• This question is taken from interviewquery. checkout for more questions.
• Crack SQL interview with Grokking the SQL Interview.