Structured Query Language (SQL) is one of the most in-demand skills for data analysts, database administrators, and software engineers. While basic queries test your knowledge of SELECT, JOIN, and WHERE clauses, tricky SQL interview questions are designed to test your creativity, problem-solving ability, and understanding of advanced SQL concepts such as window functions, subqueries, and aggregations.
In this article, we’ll go through 10 tricky SQL interview questions, each with a practical example and explanation.
1. Write a query to find the second-highest salary in an employee table without using LIMIT, OFFSET, or TOP.
SELECT MAX(salary)
FROM employees
WHERE salary NOT IN (SELECT MAX(salary) FROM employees);Explanation:
The inner query finds the highest salary. The outer query finds the maximum salary that is not equal to the highest one, effectively giving the second-highest salary.
2. Write a query to display all employees whose salary is higher than their manager’s.
SELECT e1.name AS Employee, e1.salary, e2.name AS Manager, e2.salary AS Manager_Salary
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id
WHERE e1.salary > e2.salary;Explanation:
The query joins the employees table to itself — comparing each employee’s salary to their manager’s salary.
3. How do you find duplicate rows in a table without using GROUP BY?
SELECT *
FROM employees e1
WHERE EXISTS (
SELECT 1
FROM employees e2
WHERE e1.name = e2.name
AND e1.id <> e2.id
);Explanation:
This query uses a self-join to find records that share the same values (e.g., name) but have different IDs — identifying duplicates without grouping.
4. How would you find the top 10% of earners in a company?
SELECT *
FROM employees
WHERE salary >= (
SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary)
FROM employees
);Explanation:
The function PERCENTILE_CONT(0.9) finds the 90th percentile salary. All employees earning equal to or more than this threshold are in the top 10%.
5. Write a query to calculate the cumulative (running) total of salaries.
SELECT
name,
salary,
SUM(salary) OVER (ORDER BY id) AS cumulative_salary
FROM employees;Explanation:
The SUM() OVER() function calculates a running total of salaries based on the specified order.
6. List all employees who never applied for a leave.
SELECT *
FROM employees
WHERE id NOT IN (SELECT employee_id FROM leaves);Explanation:
This uses a subquery to exclude employees present in the leaves table — i.e., those who have taken leave.
7. Write a query to calculate the difference between consecutive salary values.
SELECT
name,
salary,
salary - LEAD(salary) OVER (ORDER BY salary DESC) AS salary_diff
FROM employees;Explanation:
The LEAD() function accesses the next row’s value, enabling comparison between the current and next rows.
8. Display all departments that have more than one employee.
SELECT department
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;Explanation:
HAVING COUNT(*) > 1 filters out departments with only one employee.
9. Can you find the highest salary per department without using GROUP BY?
SELECT DISTINCT department,
FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS max_salary
FROM employees;Explanation:
Using the window function FIRST_VALUE() with PARTITION BY retrieves the maximum salary within each department without grouping.
10. Display all employees who took more than 3 leaves in any given month.
SELECT employee_id, MONTH(leave_date) AS month, COUNT(*) AS total_leaves
FROM leaves
GROUP BY employee_id, MONTH(leave_date)
HAVING COUNT(*) > 3;Explanation:
This query counts the number of leaves per employee per month and filters out those with more than three leaves.
Conclusion
These tricky SQL interview questions go beyond basic querying — they test your understanding of subqueries, joins, window functions, and analytical SQL. Mastering these will help you stand out in interviews for roles such as Data Analyst, Database Engineer, or Backend Developer.
