6. Main Office Assignment for Each Employee (Easy)

Grokking SQL for Tech Interviews

Ask Author

Back to course home

0% completed

Vote For New Content

6. Main Office Assignment for Each Employee (Easy)

Table of Contents

Contents are not accessible

Problem Statement

Table: OfficeAssignment
This table records the office assignments of employees. Each record includes an employee ID, office ID, and a main office flag indicating whether the office is the employee's main office.

+---------------+----------+
| Column Name   | Type     | 
+---------------+----------+
| employee_id   | int      |
| office_id     | int      |
| main_flag     | varchar  |
+---------------+----------+
(employee_id, office_id) is the primary key for this table.
employee_id is the ID of the employee.
office_id is the ID of the office assigned to the employee.
main_flag is an ENUM type of ('Y', 'N'). If the flag is 'Y', the office is the employee's main office. If the flag is 'N', the office is not the main one.

Employees can be assigned to multiple offices, but they can designate only one office as their main office. If an employee is assigned to only one office, that office is automatically considered their main office, regardless of the main flag.

Develop a solution to report all employees along with their main office. For employees assigned to only one office, report that office as their main office.

Return the result table in the order of employee_id.

Example

Input:

OfficeAssignment table:
+-------------+-----------+----------+
| employee_id | office_id | main_flag|
+-------------+-----------+----------+
| 1           | 101       | N        |
| 2           | 101       | Y        |
| 2           | 102       | N        |
| 3           | 103       | N        |
| 4           | 102       | N        |
| 4           | 103       | Y        |
| 4           | 104       | N        |
+-------------+-----------+----------+

Output:

+-------------+-----------+
| employee_id | office_id |
+-------------+-----------+
| 1           | 101       |
| 2           | 101       |
| 3           | 103       |
| 4           | 103       |
+-------------+-----------+

Try It Yourself

MYSQL

. . . .

Solution

To determine the main office for each employee from the OfficeAssignment table, we can follow a systematic approach that prioritizes the main office flag and handles cases where employees are assigned to only one office.

Approach Overview

Identify Main Office:
- For employees with multiple office assignments, select the office marked as main ('Y').
- For employees with only one office assignment, designate that office as their main office regardless of the main_flag.
Order Results:
- Ensure the final output is ordered by employee_id.

SQL Query

SELECT employee_id, office_id
FROM (
    SELECT employee_id, office_id,
           ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY main_flag DESC) as rn
    FROM OfficeAssignment
) AS T
WHERE rn = 1
ORDER BY employee_id;

Step-by-Step Approach

Step 1: Determine Main Office

Identify the main office for each employee by prioritizing offices marked with 'Y' in the main_flag. For employees with only one office assignment, that office is automatically considered the main office.

SQL Query:

SELECT employee_id, office_id
FROM (
    SELECT employee_id, office_id,
           ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY main_flag DESC) as rn
    FROM OfficeAssignment
) AS T
WHERE rn = 1;

Explanation:

ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY main_flag DESC) as rn:
- Assigns a unique sequential number to each office assignment within each employee_id partition.
- Orders the assignments by main_flag in descending order, ensuring that offices marked with 'Y' receive a higher priority (ROW_NUMBER = 1).
FROM OfficeAssignment:
- Specifies the source table.
WHERE rn = 1:
- Filters the result to include only the top-ranked office assignment for each employee, effectively selecting the main office.

Output After Step 2:

+-------------+-----------+
| employee_id | office_id |
+-------------+-----------+
| 1           | 101       |
| 2           | 101       |
| 3           | 103       |
| 4           | 103       |
+-------------+-----------+

Step 2: Order Results by Employee ID

Sort the identified main offices in ascending order based on employee_id to produce the final output.

SQL Query:

SELECT employee_id, office_id
FROM (
    SELECT employee_id, office_id,
           ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY main_flag DESC) as rn
    FROM OfficeAssignment
) AS T
WHERE rn = 1
ORDER BY employee_id;

Explanation:

ORDER BY employee_id:
- Ensures that the final output is sorted in ascending order of employee_id for clarity and consistency.
Combined Query:
- The query from Step 2 is enhanced with the ORDER BY clause to sort the results as required.

Final Output:

+-------------+-----------+
| employee_id | office_id |
+-------------+-----------+
| 1           | 101       |
| 2           | 101       |
| 3           | 103       |
| 4           | 103       |
+-------------+-----------+

.....

Like the course? Get enrolled and start learning!

Table of Contents

Contents are not accessible