0% completed
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
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
.
- Ensure the final output is ordered by
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).
- Assigns a unique sequential number to each office assignment within each
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.
- Ensures that the final output is sorted in ascending order of
- Combined Query:
- The query from Step 2 is enhanced with the
ORDER BY
clause to sort the results as required.
- The query from Step 2 is enhanced with the
Final Output:
+-------------+-----------+ | employee_id | office_id | +-------------+-----------+ | 1 | 101 | | 2 | 101 | | 3 | 103 | | 4 | 103 | +-------------+-----------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible