Interviewer: 
You have 2 minutes to solve this SQL query. 

Find the employee(s) with the highest salary in each department without using window functions.

Assume the table structure: 
employees(employee_id, employee_name, department, salary)

Me: Challenge accepted! 💪 
SELECT 
    employee_id, 
    employee_name, 
    department, 
    salary 
FROM employees e1 
WHERE salary = ( 
    SELECT MAX(salary) 
    FROM employees e2 
    WHERE e2.department = e1.department 
);

💡 Explanation: 
This query uses a correlated subquery instead of a window function.

• The outer query processes each employee
• The correlated subquery finds the maximum salary within that employee's department
• If the employee's salary matches the maximum salary, the employee is returned
• If multiple employees share the highest salary in a department, they are all included

This question tests your understanding of: 
• Correlated Subqueries
• Aggregate Functions (MAX)
• Filtering with Subqueries
• Handling Ties

🎯 Expected Output Example: 
John     | IT      | 95,000 
Alice    | IT      | 95,000 
Sarah    | HR      | 82,000 
David    | Finance | 91,000 

John and Alice are both returned because they share the highest salary in the IT department.

🚀 Alternative Using a Self Join 
SELECT 
    e1.employee_id, 
    e1.employee_name, 
    e1.department, 
    e1.salary 
FROM employees e1 
LEFT JOIN employees e2 
    ON e1.department = e2.department 
   AND e1.salary < e2.salary 
WHERE e2.employee_id IS NULL; 

This solution works by eliminating employees who have someone in the same department with a higher salary. The remaining employees are the highest-paid in their respective departments.

🚀 Tip for SQL Job Seekers: 
Interviewers often restrict certain SQL features like window functions or CTEs to evaluate your understanding of alternative approaches. Be prepared to solve the same problem using: 
• Correlated Subqueries
• Self Joins
• CTEs
• Window Functions

Knowing multiple solutions demonstrates strong SQL fundamentals.

❤️ React with ❤️ for more SQL interview challenges!