Top 13 SQL Scenario Based Interview Questions with Answers (2024)

According to a 2021 developer survey, each of the top four most common database management systems uses SQL. If you have an interview coming up for a data-based role, you should expect and practice SQL interview questions.

SQL scenario-based questions don’t just test your knowledge of SQL queries, but also how well you can apply them in certain situations. The situations or scenarios used will typically mimic real-world problems, and you’ll need your technical and comprehension skills to tackle them.

These scenario-based SQL interview questions can be classified as easy, intermediate, or hard, based on: The difficulty in defining or understanding the problem. The complexity of the query is required to solve the problem.

We’ve compiled 13 SQL scenario-based interview questions you can practice to pinpoint your skill level and improve. The problems and solutions are well-explained to assist you in understanding how you can approach similar questions in an actual interview!

Easy SQL scenario based interview questions will test your ability to apply simple queries and solve basic problems. There will be limited use of subqueries or multiple tables.

1. Write a query to display the highest salaries in each department.

More context: You have been provided with a table called ‘salaries’ that has three columns: EmployeeID, Department, and Salary.

The goal here is to list the single highest salary in each department. The output will consist of one column for the department and another column for the highest salary paid to a person in that department.

If there are four UNIQUE departments in the table, there should be four rows in the output.The GROUP BY function can be used to solve this problem as follows:

SELECT Department, MAX(Salary) FROM salaries 
GROUP BY Department

2. Several employees are yet to be assigned a department in your company. How would you find their details?

This question tests two different things. The first is a basic ability to filter data. You are looking through a list of employees to find only those who meet a particular criterion, in this case, if they have been assigned a department. The WHERE clause is commonly used for this purpose in SQL.

This question also tests whether you know how empty entries are stored in an SQL database. In SQL, empty values are stored as NULL, which can be used to locate empty entries by pairing the WHERE clause and the IS NULL operator.

For this question, you can find all employees without assigned departments with a query like:

SELECT * FROM employee_table
WHERE department_column IS NULL

3. Show all employees hired between 2015 and 2020.

When you need to find records of activities that took place within a specific time frame, you can use the BETWEEN comparator in SQL. It’s important to remember the format of the date used in the database. Here’s how you can write the query:

SELECT * FROM employee_table
WHERE employment_Date BETWEEN '2015-01-01' AND '2020-12-31';

Note: Although dates are compared like other values, they still need the quotes around them to avoid errors.

4. How would you display the records of the top 15 students in the most recent exam?

Some of the tables you’ll be working with will contain thousands or even millions of records. You’ll often want to limit the amount of data displayed, so you can preview the relevant records efficiently.

This can be quickly achieved by employing the LIMIT clause in SQL. For this question, this could simply be:

SELECT * from students_table
LIMIT 15

While the above query will yield a result, it may not be the correct one. The question asks for the “top”’ students. This means that the student’s records must first be organized from highest to lowest based on their examination results before a LIMIT clause is applied.

This can be handled using the ORDER BY clause as shown below:

SELECT * from students_table
ORDER BY exam_results DESC
LIMIT 15

5. You have been tasked with finding new sales areas to target. Find the neighborhoods with no current users.

More context: You have been given a ‘users’ table and a ‘neighborhoods’ table.

The fact that you have two tables means you’ll probably need to use a JOIN clause, but that’s just half the story.

The neighborhoods being asked about in this question are those with no existing users. This means that you need to join the two tables and find the neighborhoods that appear on the ‘neighborhoods’ table but don’t have a single user in them in the ‘users’ table.

Assuming the ‘neighborhoods’ table is on the left, you can use LEFT JOIN to identify all the neighborhoods. The WHERE clause can then be used to filter out any neighborhood that shows up in the ‘users’ table.

Hint: The IS NULL operator can be handy in solving this problem.

6. Find the last record in a table of daily transactions.

More context: You have a single table with columns for the transaction ID, the time of the transaction, and the value of the transaction.

Like many questions in data science, there is more than one way to arrive at the answer to this question. A straightforward approach is to use a combination of data sorting and the limit function.

Date and time data can be sorted in ascending or descending order with the latest date-time considered the higher value. With this in mind, you can find the last transaction on a given day using the query below:

SELECT * FROM transactions_table
ORDER BY transaction_time DESC
LIMIT 1

The query above will display only one record, and, thanks to the DESC clause, it will be the transaction with the highest date-time value.

An alternative approach to answering this question uses the MAX function. Can you figure out what the query will be?

Intermediate SQL scenario based interview questions will push your comprehension and query writing skills. These scenarios will usually require working with two or more tables. You’ll also want to take time to understand the problem posed and the possible ways of solving it, before jumping into possible solutions.

7. Write a query to display each city where the company has customers from two different states and the number of customers in each city.

More context: You have been provided with two tables with identical columns for two different states. The tables contain columns for customer ID, and their cities within the two states.

The count() function and the GROUP BY clause would have sufficed if all the records were on one table. However, this problem is slightly more challenging since the records are in two separate tables.

You can solve this question by taking advantage of subqueries. In this case, the UNION ALL clause can be used in the subquery to append the customers’ cities from the two tables. The customer IDs from each city are then grouped and counted.

The subquery used in the FROM clause must have an alias so it can be referenced in the query. The query would look as shown below:

SELECT complete_list.City, COUNT(complete_list.customerID)
FROM (
    SELECT City FROM Nevada_customers
    UNION ALL
    SELECT City FROM Texas_customers
) AS complete_list
GROUP BY complete_list.City;

Ensure you use the UNION ALL and not the UNION clause. The latter would append each city once to the combined list of cities, leading to each city showing just one customer.

8. How can you identify the customers who have made more than ‘X’ number of purchases in the past year?

Context: You have a purchases table and a users table

Once again, having two tables usually means needing to use a UNION or JOIN clause. In this case, the purchases table should have a customer ID column to uniquely identify which customer made which purchase.

Based on this, an INNER JOIN can be performed on the two tables, outputting a table with transaction IDs and the IDs of the customers who made the transactions.

The transactions can then be grouped by the customer IDs and the number of transactions associated with each customer added.

This question tests your ability to use JOINs and Subqueries, as well as your ability to apply the GROUP BY clause with a filter.

Hint: You may need to use the HAVING clause.

9. How would you write a query to find the second or third-highest bonuses paid out the previous month?

This type of question is common and it can be solved in different ways. Some solutions are not as elegant as others but the logic is easy to follow..

In the less elegant approach, you can use subqueries or nested queries to find the maximum bonus in a list of all bonuses that are less than the maximum bonus. The query to find the second-highest bonus using this approach looks like this:

SELECT MAX(Bonus) from employee_bonuses
WHERE Bonus<(SELECT MAX(Bonus) from employee_bonuses)

By adding an identical subquery inside the first subquery, you can create the query that gives the third highest bonus. This can look like this:

SELECT MAX(Bonus) from employee_bonuses
WHERE Bonus<(SELECT MAX(Bonus) from employee_bonuses
WHERE Bonus<(SELECT MAX(Bonus) from employee_bonuses))

The more elegant approach is to order the bonuses from highest to lowest, limit the result to one, and use an offset to choose the 2nd or 3rd highest bonuses as shown here:

SELECT Bonus from employee_bonuses
ORDER BY Bonus DESC LIMIT 1 OFFSET 1;

You can change the offset to 2 to see the third-highest bonus or change the limit to 2 to view the second and third-highest bonuses at the same time.

At this level, you can expect SQL scenario based interview questions to be highly challenging. The scenario may not be well-defined and may require you to fill in some blanks with informed assumptions. Furthermore, the questions may be straightforward, but will require using specific functions that a beginner may not be familiar with.

10. Suggest a new online connection based on mutual likes and mutual friends.

More Context: You’ve been provided with four tables; ‘users’, ‘friends’, ‘likes’, and ‘blocked’. You are to suggest one friend to a user in the ‘users’ table based on the number of mutual friends and mutually liked pages. Current friends and anyone already blocked by the user are disqualified. Each mutual friend gives a potential friend 3 points, and each mutually liked page gives them 2 points.

This is the type of question you may encounter in an interview for a social media company where a key goal is to get users to connect with others. The idea here is that two people are more likely to connect if they like similar things or associate with the same people.

Solving this question will require you to identify the chosen user’s existing friends, their page likes, and users they’ve already blocked. You’ll also need to calculate the points to be assigned to the other users for mutual friends and likes so you can identify those with the highest points.

Finally, you’ll need to compile the list of potential friends, rank them, and eliminate existing friends or blocked individuals.

HINT: Common table expressions (CTEs) can help tackle the different operations needed to complete this task.

11. Calculate the daily three-day rolling average for deposits made to a bank.

More Context: You have been provided with a table containing columns for user ID, transaction amount, and time of transaction in datetime format. The positive transactions in the table are deposits and the negative transactions are withdrawals. The date in the output should be in the format ‘%Y-%m-%d’

There are different scenarios where calculating rolling averages would be important. A similar question could show up when interviewing for companies involved in stock or crypto trading. The most important columns in this table are for transactions and dates.

The first step to solving this problem is to sum the deposits made on each date. We can do this with the help of a CTE.

WITH daily_deposits AS (
	SELECT DATE_FORMAT(transaction_time, ‘%Y-%m-%d’) AS dt,
SUM(transaction_amt) AS deposits
FROM bank_transactions AS bt
WHERE transaction_amt>0
GROUP BY 1
)

SELECT * FROM daily_deposits

The above code outputs the total amount of deposits for each day. The next challenge is calculating the rolling 3-day average. If you had to use a JOIN clause, how would you solve this second part of the problem?

12. Write a query that repeats an integer as many times as the value of the integer itself.

More Context: You have a table with a column of integers. If the number in a row is 3, the integer 3 should be repeated three times in the output. This should be done sequentially for each integer in the table.

When an interviewer asks this type of question, they are probably testing your knowledge of advanced SQL queries. This type of problem can be solved in languages like Python using loops. In SQL you can employ the concept of recursion instead. This uses the RECURSIVE clause.

Recursion is a unique form of looping where the output from one iteration becomes the input for the next iteration.

In this scenario, the output of each iteration is a counter and the unchanged integer from the table. If the integer in the table is three, the first iteration will select 3 and a counter.

The value of the counter is the only one that changes in each iteration, but each time, the value of the number from the table, 3, is selected until the value of the counter is equal to it.

13. Write a query to calculate the percentage of users who recommended each page and are in the same postal code as that page.

More Context: There are three tables provided - ‘page_sponsorships’, ‘recommendations’, and ‘users’. The ‘sponsorship’ table has columns for page_id, postal_code, and price, i.e., the value of the sponsorship. The ‘recommendations’ table contains the user_id and the page_id of the recommended page. The ‘users’ table holds the user IDs and their postal codes. Pages are allowed to sponsor more than one postal code.

This type of question will test your comprehension. Several criteria must be met by the output and they require inputs from all three tables.

A distinction must be made between users who recommend a page and share its sponsored postal code and those who recommend the page and don’t share its postal code. The SUM CASE WHEN conditional statement can be handy here.

All three tables will have to be joined so the postal codes of the users who recommended a page and those sponsored by the page can be compared.

Grouping will have to be done based on two columns to separately aggregate users in different postal codes sponsored by the same page.

You may find scenario based SQL questions challenging, but with the right steps, you can learn to handle them with ease. Here are some ways you can improve your success rate with SQL scenario based questions.

1. Know what the question is asking before you start

Scenario based questions are used to test your understanding, and not just your ability to write SQL queries. Go over the question a few times and try to distill the question down to its simplest parts.

2. Clarify Assumptions

The interviewer may be targeting a specific answer when asking a specific question. Confirm any assumptions you may have and get a clearer picture of what the problem is.

3. Consider Similar Problems

The questions above reveal that many problems in SQL can be solved through similar steps. For example, questions involving multiple tables will require that the tables be joined in some way and a filter applied to the data left after joining.

4. Think of the steps logically

When working with subqueries, which will be often, it helps to think of the steps logically. Some actions cannot be performed unless other actions have been performed first. This can give you an entry point that allows you to work towards the final solution.

5. Practice writing queries

There are more scenarios that an interviewer can come up with than anyone could possibly cover. That’s why the best way of getting better at solving SQL scenario based interview questions is to answer as many practice questions as possible.

Practicing more SQL questions will allow you to master what you already know, and expose you to approaches you may never have considered.