Primary Department for Each Employee

Primary Department for Each Employee

What is this problem about?

The Primary Department for Each Employee SQL problem asks you to find the primary department for each employee. An employee's primary department is either flagged as primary ('Y') in the Employee table, or if they belong to only one department, that department is primary by default. This easy SQL problem tests UNION and conditional logic. The database interview pattern is demonstrated.

Why is this asked in interviews?

Microsoft, Meta, Amazon, Google, and Bloomberg ask this because it tests the ability to handle two cases in a single query: employees with an explicit primary flag and employees with only one department. The UNION approach cleanly handles both.

Algorithmic pattern used

UNION of two cases. Case 1: SELECT employee_id, department_id FROM Employee WHERE primary_flag = 'Y'. Case 2: Employees with only one department (no primary flag): SELECT employee_id, department_id FROM Employee GROUP BY employee_id HAVING COUNT(*) = 1. UNION both result sets.

Example explanation

Employee: emp1(dept1,'Y'), emp1(dept2,'N'), emp2(dept3,'N'). Case 1: emp1→dept1 (has 'Y' flag). Case 2: emp2 belongs to only dept3 (COUNT=1). Include emp2→dept3. Result: [(emp1,dept1),(emp2,dept3)].

Common mistakes candidates make

• Using WHERE primary_flag = 'Y' without handling the single-department case.
• Not using UNION (accidentally filtering out single-department employees).
• Including duplicate rows (use UNION, not UNION ALL, though in this problem there shouldn't be duplicates).
• Joining instead of using UNION for the two cases.

Interview preparation tip

Two-case SQL problems are best solved with UNION: identify the two mutually exclusive conditions, write each as a separate query, combine with UNION. Here: explicit primary ('Y') OR only-one-department. Practice similar "default value when primary not specified" SQL patterns — they appear in user preference, address, and contact management schemas.