most-common-sql-2022

4 Most Common SQL Interview Problems and Solutions in 2022

Tim S

Tim S

Tim currently works at FAANG as a data analyst. He's been in the industry for 5+ years and writes for Big Tech Interviews on the weekends. His favorite language is SQL and calls Denver, CO home.

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

employee-building-tables

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

employee-building-tables

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

employee-building-tables

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

employee-building-tables

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.

Similar Articles