SQL Interview Patterns

1. Nth Highest Salary

Find the second highest salary from the Employee table.

-- Solution 1: using SUBQUERY
SELECT MAX(Salary) FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee);

-- Solution 2: using LIMIT/OFFSET (MySQL/PostgreSQL)
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;

-- Solution 3: using DENSE_RANK() (Generic)
SELECT Salary FROM (
    SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) as Rank
    FROM Employee
) as RankedSalaries
WHERE Rank = 2;

2. Duplicate Emails

Find all emails that appear more than once.

SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(Email) > 1;

3. Employees Earning More Than Managers

Given Employee table with Id, Name, Salary, and ManagerId.

SELECT e.Name as Employee
FROM Employee e
JOIN Employee m ON e.ManagerId = m.Id
WHERE e.Salary > m.Salary;

4. Delete Duplicate Rows

Keep only the row with the smallest Id for each Email.

DELETE p1 FROM Person p1, Person p2
WHERE p1.Email = p2.Email AND p1.Id > p2.Id;

5. Department Highest Salary

Find employees who have the highest salary in each of the departments.

SELECT Department, Employee, Salary
FROM (
    SELECT d.Name as Department, e.Name as Employee, e.Salary,
    DENSE_RANK() OVER (PARTITION BY e.DepartmentId ORDER BY e.Salary DESC) as Rank
    FROM Employee e
    JOIN Department d ON e.DepartmentId = d.Id
) as Ranked
WHERE Rank = 1;