most-common-sql-2022

Common SQL Problems and Solutions (Updated 2024)

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

If you’ve ever found yourself scratching your head over error messages, slow queries, or unexpected results, fear not – you’re not alone. In this guide, we’ll dive into the realm of Common SQL Problems and Solutions, unraveling the mysteries that often leave even seasoned developers puzzled. SQL, or Structured Query Language, is a powerful tool for interacting with databases, but it’s not without its challenges. Whether you’re a newcomer or a seasoned pro, chances are you’ve encountered a few roadblocks along the way. The good news? Every hiccup in your SQL journey is an opportunity to learn and improve. In this article, we’ll shine a light on the most common SQL pitfalls and provide you with practical, upbeat solutions to keep your databases running smoothly. Get ready to conquer those SQL obstacles with confidence and a dash of SQL magic! you ready to find out the most common SQL interview Problems asked in 2022 thus far?

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;

 

Learn More: How to Avoid Dividing by Zero in MySQL 

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;

 

Read More: Google Data Scientist Interview Questions 

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. 

Read More: Google SQL Interview Questions

Do you want to ace your SQL interview?

Practice free and paid real SQL interview questions with step-by-step answers.

Do you want to Ace your SQL interview?

Practice free and paid SQL interview questions with step-by-step video solutions!