Rich Text Body:
Sometimes the best way to prepare for an interview is to practice with real-world examples. In SQL interviews (especially for analyst or developer roles), you may be asked to write actual queries to solve problems. Many candidates have shared their experiences on forums like Reddit about the practical SQL questions they encountered. Here, we’ll walk through a few real SQL query challenges that have been reported in interviews, and discuss how to solve them. These examples range from simple to tricky, mirroring tasks an interviewer might give you to test your query-writing skills.
(Tip: Interviewers are often more interested in your approach and reasoning than just the final answerreddit.com. As you practice, focus on writing correct SQL and be ready to explain why you chose that approach.)
Question: “Given an Employee table with an Id primary key and a ManagerId field that refers to the Id of that employee’s manager, write a query to find all managers who have 5 or more direct reports.” This question appeared on a forum as a challenge someone faced during an interviewdiscuss.codecademy.com. It tests your ability to use self-joins or subqueries and aggregation.
Solution: One way to solve this is to group employees by their manager and count them. We can use a subquery or HAVING clause on a grouped result to filter managers with count ≥ 5. For example, using a subquery in the WHERE clause:
SELECT Name
FROM Employee
WHERE Id IN (
SELECT ManagerId
FROM Employee
GROUP BY ManagerId
HAVING COUNT(*) >= 5
);
This finds all Id values that appear as a ManagerId at least 5 times (thus those Ids belong to managers with ≥5 reports)discuss.codecademy.com. However, it might be more efficient to avoid the IN and use a JOIN. A better approach:
SELECT m.Name AS ManagerName, COUNT(e.Id) AS TeamSize
FROM Employee m
JOIN Employee e
ON e.ManagerId = m.Id
GROUP BY m.Name, m.Id
HAVING COUNT(e.Id) >= 5;
This query joins the Employee table to itself (alias m for managers, e for employees) and groups by the manager. The HAVING COUNT(e.Id) >= 5 ensures only those with 5 or more direct reports are returned. It will output each manager’s name and the number of direct reportsdiscuss.codecademy.com.
Either approach would be acceptable in an interview: the first one is more straightforward, and the second one is more performant for large tables. You should mention that the second query is doing the counting in one pass (and could use an index on ManagerId if present). Interviewers love when you can discuss alternatives and their trade-offs (here, subquery with IN vs. join with HAVING).
Question: “Find the second highest salary from the Employees table.” This is a classic interview question that tests whether you know how to use subqueries or window functions to rank results. Variations include finding the Nth highest value.
Solution: There are multiple ways to get the 2nd highest salary:
SELECT MAX(salary)
FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees);
DISTINCT or a different approach to handle ties.)ORDER BY and LIMIT: In MySQL or Postgres, you can do:
SELECT salary
FROM Employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
SELECT TOP 1 salary ... WHERE salary NOT IN (SELECT MAX(salary) ...) or use window functions as below.)RANK() function:
SELECT *
FROM (
SELECT Name, Salary,
RANK() OVER (ORDER BY Salary DESC) as salary_rank
FROM Employees
) sub
WHERE salary_rank = 2;
DENSE_RANK() or perhaps the subquery method abovejavarevisited.blogspot.com. Assuming we interpret “second highest” as the second distinct value, the above works. It’s a bit advanced for some entry-level interviews, but showcasing a window function solution can impress interviewers if appropriate.Each method has its use. In an interview, you could mention and even write two approaches – for instance, the subquery method and the window function – explaining which you prefer and why. The subquery is simpler conceptually, while the window function is very powerful if you’ve learned it (and also useful for more complex scenarios).
Question: “We have two tables: Customers and Orders. How can you find the customers who have never placed an order?” This is a common question for analysts or developers, since it involves an anti-join (finding records in one table not matched by another). It’s often asked in the context of understanding LEFT JOIN vs INNER JOIN, or testing your knowledge of subqueries vs joins for this kind of problemforums.sqlteam.com.
Solution: The typical solution is to use a LEFT JOIN and then filter where the right side is NULL, because a NULL in the joined columns indicates no match. For example:
SELECT c.customer_name
FROM Customers c
LEFT JOIN Orders o
ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
This will return all customers for whom the join found no corresponding order (i.e., order_id is NULL because no order exists for that customer). In other words, customers with no ordersforums.sqlteam.com.
Another approach is to use a subquery with NOT EXISTS or NOT IN, for instance:
SELECT customer_name
FROM Customers c
WHERE NOT EXISTS (
SELECT 1 FROM Orders o
WHERE o.customer_id = c.customer_id
);
This returns customers for which the subquery finds no orders. It achieves the same result as the LEFT JOIN method. You could also do WHERE c.customer_id NOT IN (SELECT customer_id FROM Orders) which is succinct, though be mindful of how it handles NULLs (if a customer_id in Orders were null, that approach can behave unexpectedly; in this scenario, customer_id is likely NOT NULL by design).
In an interview setting, the LEFT JOIN/NULL approach is very clear and is a good answer. You might add that this kind of query is a “find unmatched” pattern and that the database will use an anti-join operation under the hood. Showing knowledge of both JOIN and subquery solutions – and noting that the join is generally more straightforward – would cover all bases.
(This example is a bit more advanced but has appeared in interviews and online forums.) Question: “List each department and the name of the employee with the highest salary in that department.” This requires finding a maximum within each group and reporting the associated row’s data – a great test of using window functions or correlated subqueries.
Solution: We can solve this using a window function to rank or sort salaries by department:
SELECT department, employee_name, salary
FROM (
SELECT department, employee_name, salary,
ROW_NUMBER() OVER (PARTITION BY department
ORDER BY salary DESC) as rn
FROM Employees
) sub
WHERE rn = 1;
This uses ROW_NUMBER() partitioned by department to assign 1, 2, 3, … within each department ordered by salary descending. Then we take rn = 1 which means the highest salary in each departmentdatalemur.comcodesignal.com. This yields one top-paid employee per department. If there are ties and you wanted all top earners, you could use RANK() instead and check rank = 1 (which would give multiple people if they tied for top salary).
An alternative approach is a correlated subquery:
SELECT e.department, e.employee_name, e.salary
FROM Employees e
WHERE e.salary = (
SELECT MAX(salary)
FROM Employees
WHERE department = e.department
);
This finds employees whose salary equals the max salary of their department. This works too (and will return all top earners if there’s a tie in a department). It’s a bit easier to read for some, although it might do more work if departments are large (since the subquery runs per employee, though many SQL engines optimize this type of pattern).
In an interview, either answer is good. The window function approach is more modern and flexible; the subquery approach is classical and quite clear. If you give one, it’s a bonus if you can mention the other as an alternative.
These examples mirror real interview scenarios discussed by candidates and interviewers online. Interviewers often start with something straightforward (like a simple join or filter) and then increase the difficulty, or add twists (what if there’s a tie? can you do it without subqueries? etc.). By practicing these problems, you’ll not only be writing correct SQL but also be prepared to talk through different solutions.
Lastly, always explain your reasoning as you code. For instance, if you choose a LEFT JOIN solution, note that “I’m using LEFT JOIN ... WHERE IS NULL to find non-matches,” which shows you understand the conceptforums.sqlteam.com. This habit demonstrates clear thought, which is exactly what interviewers are looking forreddit.com.
Practice these queries on your own database or Galaxy’s SQL Playground to be comfortable with them. Happy querying!
.avif){kind=logo}
