logo

Back to questions

Uniquely Staffed Consultants

Medium

As a Data Analyst on the People Operations team at Accenture, you are tasked with understanding how many consultants are staffed to each client, and how many consultants are exclusively staffed to a single client.

Write a SQL query that outputs the client name, the number of uniquely staffed consultants, and the number of exclusively staffed consultants.

Tip:

  • Order the dataset by client name!

Table:

Column NameType
employee_idinteger
engagement_idinteger

Example Input:

employee_idengagement_id
10011
10012
10021
10033
10044

Table:

Column NameType
engagement_idinteger
project_namestring
client_namestring

Example Input:

engagement_idproject_nameclient_name
1SAP Logistics ModernizationDepartment of Defense
2Oracle Cloud MigrationDepartment of Education
3Trust & Safety OperationsGoogle
4SAP IoT Cloud IntegrationGoogle

Example Output:

client_nametotal_staffedexclusive_staffed
Department of Defense21
Department of Education10
Google22

Explanation:

The Department of Defense has only 1 project, which is staffed by employees 1001 and 1002. However, employee 1001 is also working on project 2. So, in total, there are 2 employees working on the project, but only 1 is exclusively working with the Department of Defense.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.