Back to questions
Uniquely Staffed Consultants Accenture SQL Interview Question
Uniquely Staffed Consultants
Accenture SQL Interview Question
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 specifically how many consultants are exclusively staffed to a single client.
Write a query that displays the client name along with the total number of consultants attached to each client, and the number of consultants who are exclusively staffed to each client (consultants working exclusively for that client). Ensure the results are ordered alphabetically by client name.
As of July 5th, the datasets have been expanded to cover edge cases and the solutions have been revised accordingly.
Table:
| Column Name | Type |
|---|---|
| employee_id | integer |
| engagement_id | integer |
Example Input:
| employee_id | engagement_id |
|---|---|
| 1001 | 1 |
| 1001 | 2 |
| 1002 | 1 |
| 1002 | 8 |
| 1003 | 3 |
| 1003 | 4 |
| 1004 | 3 |
| 1004 | 4 |
| 1005 | 5 |
| 1005 | 6 |
| 1005 | 7 |
Table:
| Column Name | Type |
|---|---|
| engagement_id | integer |
| project_name | string |
| client_name | string |
Example Input:
| engagement_id | project_name | client_name |
|---|---|---|
| 1 | SAP Logistics Modernization | Department of Defense |
| 2 | Oracle Cloud Migration | Department of Education |
| 3 | Trust & Safety Operations | |
| 4 | SAP IoT Cloud Integration |
Example Output:
| client_name | total_consultants | single_client_consultants |
|---|---|---|
| Department of Defense | 2 | 1 |
| Department of Education | 1 | 0 |
| 2 | 2 |
Explanation:
- Department of Defense: Total consultants are 2 (1001 and 1002), and 1 consultant (1002) is exclusively staffed.
- Department of Education: Total consultant is 1 (1001), but not exclusively staffed as 1002 is also staffed to engagement ID 8.
- Google: Total consultants are 2 (1003 and 1004), and both consultants are exclusively staffed.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Step 1: Identify Consultants Exclusively Staffed to a Single Client
To start, we identify consultants who exclusively work with a single client.
We achieve this by joining the table with , grouping by , and filtering for those with engagements limited to one client using .
By using , we accurately determine if an employee is exclusively staffed to a single client by ensuring each client is counted only once in the grouping.
This query is then encapsulated in a CTE named :
Learn more about the nuances between and clauses in SQL.
Step 2: Calculate Total Consultants
Next, we calculate the total number of employees staffed to each client. Similar to Step 1, we join with , group by , and count the distinct s:
Step 3: Combine and Analyze Results
Finally, we combining the datasets from Steps 1 and 2 using a which ensures all clients are included. We then calculate the count of consultants exclusively staffed () for each client. The results are ordered by .