Uniquely Staffed Consultants
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.
|1||SAP Logistics Modernization||Department of Defense|
|2||Oracle Cloud Migration||Department of Education|
|3||Trust & Safety Operations|
|4||SAP IoT Cloud Integration|
|Department of Defense||2||1|
|Department of Education||1||0|
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.
The most difficult part of this task is to determine the employees who are exclusively staffed to one company. Fortunately, it sounds harder than it actually is.
Step 1: Exclusive Staff
Let's start off by counting the clients each employee is working for, and then keep only the employees who have just 1 single client. First, we'll join the table with , and then filter by the count of . We'll wrap the following query in a CTE called so we can use the results later:
Click here to learn more about the difference between and in SQL.
Step 2: Employees to Clients
Next, let's find the total count of employees staffed to each client. We'll perform the same as in Step 1, and then instead count the s by client:
We can already see the finish line! We have the IDs for employees working with only one client, and we have the total counts of employees per client. Now we can join the two datasets from steps 1 and 2!
Make sure you use to keep all of the rows from ! Then, add a new statement that counts the employees from the CTE - this will show the number of exclusively staffed employees. Finally, don't forget to order the dataset by client names!