SQL Queries Asked in Interviews

Most data and backend interviews include a live SQL question. The queries below use a simple employees(id, name, dept_id, salary, manager_id, joined_on) and departments(id, name) schema. They run on MySQL 8 and PostgreSQL. Practise writing them without running them first — that is how most interviews go.

1. Find the employees who earn more than their manager.

A self join: join the table to itself, once as employee and once as manager.

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;

2. Find the Nth highest salary (handle ties).

DENSE_RANK() gives tied salaries the same rank without gaps, so “3rd highest” means the 3rd distinct salary.

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 3;

3. Find and delete duplicate rows, keeping one copy.

Group by the columns that define a duplicate to find them; to delete, keep the smallest id in each group.

-- Find
SELECT name, dept_id, COUNT(*) AS copies
FROM employees
GROUP BY name, dept_id
HAVING COUNT(*) > 1;

-- Delete, keeping the lowest id (MySQL 8 / PostgreSQL)
DELETE FROM employees
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY name, dept_id ORDER BY id) AS rn
    FROM employees
  ) t
  WHERE rn > 1
);

4. Show departments with more than 5 employees and their average salary.

Aggregate with GROUP BY, filter groups with HAVING, and use a LEFT JOIN if you also want departments with no employees.

SELECT d.name, COUNT(e.id) AS staff, ROUND(AVG(e.salary), 2) AS avg_salary
FROM departments d
JOIN employees e ON e.dept_id = d.id
GROUP BY d.id, d.name
HAVING COUNT(e.id) > 5
ORDER BY avg_salary DESC;

5. Find the top 2 earners in every department.

“Top N per group” is a window-function classic: rank inside each department with PARTITION BY, then filter.

SELECT dept_id, name, salary
FROM (
  SELECT dept_id, name, salary,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn <= 2;

6. Calculate a running total of salaries by joining date.

A window SUM with ORDER BY adds rows up as it goes. Unlike GROUP BY, window functions keep every row.

SELECT name, joined_on, salary,
       SUM(salary) OVER (ORDER BY joined_on, id) AS running_payroll
FROM employees
ORDER BY joined_on, id;

7. Find departments that have no employees.

Either a LEFT JOIN with an IS NULL check, or NOT EXISTS. Avoid NOT IN with a subquery that can return NULL — it then returns no rows at all.

SELECT d.name
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

8. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

For salaries 90, 80, 80, 70: ROW_NUMBER gives 1, 2, 3, 4 (always unique); RANK gives 1, 2, 2, 4 (gap after ties); DENSE_RANK gives 1, 2, 2, 3 (no gaps). Choose based on how the question treats ties.