Introduction
Are you ready to find out the most common SQL interview Problems asked in 2022 thus far?
Well, you’ve come to the right place. I’m one of the lead writers at BigTechInterviews (BTI) and we’ve analyzed over 10,000 SQL interview Problems in 2022 alone!
You won’t believe the findings we found, so grab your coffee or cup of tea and get ready to learn the 4 most common SQL Problems.
For simplicity, these 4 questions will all be centralized around two tables, the employee table, and the building table.
Table of Content
Question #1: Salary by Department
Question #2: Empty Buildings
Question #3: 2nd Highest Salary by Department
Question #4: Employees Who Started Before Their Manager
Question #1: Salary by Department
Prompt: Write a query to find the average salary by department
Difficulty: Easy
Solution for question #1
First, we need to use a GROUP BY for the employees by their department and then calculate the average salaries.
SELECT department,
avg(salary) AS avg_salary
FROM employee
GROUP BY department;
Want to practice other free questions like this? Visit bigtechinterviews.com
Question #2: Empty Buildings
Prompt: Write a query to return a list of buildings where there are no employees in them.
Difficulty: Medium
Solution for question #2
We need to find the building_id data which is not present in the employee table, we will need to utilize a LEFT JOIN and filter for NULL values in the employee table, otherwise, we’d have results that include buildings with employees. Note: A LEFT JOIN returns all the rows from the left table whether they match with the right table or not.
SELECT b.building_id,
b.building_name
FROM building b
LEFT JOIN employee e ON b.building_id = e.building_id
WHERE e.employee_id IS NULL;
Want to practice other free questions like this? Visit bigtechinterviews.com
Question #3: 2nd Highest Salary by Department
Prompt: Write a query to find the list of employees who earn the second-highest in each department.
Difficulty: Hard
Solution for question #3
To know which employees have the second-highest salary by department, the first thing we need to do is to partition the table by department, rank the salaries, and then select those employees with the rank equal to 2.
To solve this question it is important to understand how to solve a scenario where the two highest-paid employees have the exact same salary.
The desired behavior should be explained in the question. For now, we are assuming that if the two highest-paid employees have the same salary, then showing the one who has a more recent start_date.
Note: We’re using a window function, which performs a calculation across a set of table rows that are related to the current row.
Additionally, we’re intentionally using row_number instead of rank or dense_rank, because it is possible for +2 employees in the same department to have the same salary. If this happens, we are selecting the newest employee.
WITH employee_rank AS
(SELECT employee_id,
first_name,
last_name,
department,
row_number() OVER (PARTITION BY department
ORDER BY salary DESC, start_date ASC) AS rank_
FROM employee)
SELECT employee_id,
first_name,
last_name,
department
FROM employee_rank
WHERE rank_ = 2;
Want to practice other free questions like this? Visit bigtechinterviews.com
Question #4: Employees Who Started Before Their Manager
Prompt: Write a query to find all the employees who started before their manager.
Difficulty: Hard
Solution for question #4
First, we need to get the information of each manager to employee. We can do this by using a SELF JOIN with the employee table on the manager_id column (since all the managers are employees themselves), and then comparing the start_date of both employees and their managers using a row_number window function.
WITH employee_rank AS
(SELECT employee_id,
first_name,
last_name,
department,
row_number() OVER (PARTITION BY department
ORDER BY salary DESC, start_date ASC) AS rank_
FROM employee)
SELECT employee_id,
first_name,
last_name,
department
FROM employee_rank
WHERE rank_ = 2;
Want to practice other free questions like this? Visit bigtechinterviews.com
Conclusion
Alright, how did you do? Did you notice a common theme around these questions?
If you did notice it, there were several repeat use cases for window functions and self-joins. Whether you are currently a data scientist looking to make a switch or looking to break into the space you’re probably going to face similar questions in a SQL interview.
If you want to ace your next SQL interview, you can practice for free by visiting bigtechinterviews.com or browse our latest technical interview articles here.
