Department CountCompany: Amazon | Difficulty: easy

Count the number of employees in each department.

Write a query to return the department name and the total number of employees working in that department as count. Treat employees without a department as Unassigned. Sort by the employee count in descending order. If tied, sort by department name in ascending alphabetical order.

Table Schema

Table	Column	Type
`employees`	`name`	varchar(100)
`employees`	`department`	varchar(80)

Example Input: `employees`

name	department
Andy Jassy	AWS
Werner Vogels	AWS
Matt Garman	Retail
Doug Herrington	Retail
Brian Olsavsky	Finance

Example Output Explanation

AWS and Retail each have two employees, while Finance has one. Departments are sorted by employee count in descending order, with the department name used in ascending alphabetical order for ties.

SchemaDROP TABLE IF EXISTS employees; CREATE TABLE employees ( name VARCHAR(100), department VARCHAR(80) ); INSERT INTO employees VALUES ('Andy Jassy', 'AWS'), ('Werner Vogels', 'AWS'), ('Matt Garman', 'Retail'), ('Doug Herrington', 'Retail'), ('Brian Olsavsky', 'Finance');← All SQL Challenges · Amazon Questions · Learn SQL Free