Top 30 SQL Query Interview Questions

Top 30 SQL Query Interview Questions


Today we’re having a look at the top 30 SQL query interview questions and concepts that you must prepare for an SQL coding interview.

Last summer we published the ultimate guide to the SQL Interview Questions You Must Prepare. In it, we covered all the SQL areas that are, from our experience, required by the interviewers. These, along with coding, include theoretical knowledge about SQL and databases.

Now we will concentrate only on coding skills. In a way, it’s a sequel to the ultimate guide mentioned above, with this one being dedicated only to writing queries in SQL.

This singular skill will be here divided into six distinct technical concepts which will be used to solve, in total, 30 business problems.

We’ll start with the easiest, add one concept to the previous one(s) and end up with the most complex queries that include most or all of the concepts at the same time. With five questions in each category, this means covering 30 SQL query interview questions.

Here are the concepts we’ll talk about:

  • Aggregating, Grouping, and Ordering Data
  • Filtering Data
  • Joins & Unions
  • Subqueries & CTEs
  • Window Functions
  • Text & Date Manipulation

Aggregating, Grouping, and Ordering Data

SQL Questions for Aggregating, Grouping, and Ordering Data

These three SQL concepts usually come hand in hand. When you’re aggregating data, it usually means you’ll have to group it, too, and that way provide data with more insight and in a presentable form. Ordering data is also one of the tools to make data more user-friendly.

SQL Query Interview Question #1: Total Number Of Housing Units

The Airbnb question gives you the following information:

“Find the total number of housing units completed for each year. Output the year along with the total number of housings. Order the result by year in ascending order.”

Link to the question: https://platform.stratascratch.com/coding/10167-total-number-of-housing-units

The dataset consists of the table housing_units_completed_us with the following columns:

housing_units_completed_us

yearint
monthint
southfloat
westfloat
midwestfloat
northeastfloat

Take a look at the partial data below and you’ll know what you have to do in a query.

Dataset for SQL Query Interview Questions from Airbnb

The completed housing units are recorded according to the region. If you sum all the regions you get all the completed units. If you group data by the year and order it by the year ascendingly, you’ll get the correct result in the desired format.

Here’s the solution and its output.

SELECT year,
       SUM(south + west + midwest + northeast)  AS n_units
FROM housing_units_completed_us
GROUP BY 1
ORDER BY 1
Answer to SQL Query Interview Question from Airbnb

SQL Query Interview Question #2: Find the Number of Entries per Star

It’s a Yelp’s question that asks:

“Find the number of entries per star. Output each number of stars along with the corresponding number of entries. Order records by stars in ascending order.”

Link to the question: https://platform.stratascratch.com/coding/10054-find-the-number-of-entries-per-star

Here you’ll also have to aggregate data, but this time using the COUNT() function. Output the columns question asks you to, then group and order data much as in the previous example.

Why don’t you use those hints in the widget below?

Your output should look like the one below.

Answer to SQL Query Interview Question from Yelp

SQL Query Interview Question #3: Find the Average Number of Stars for Each State

This is again a question by Yelp:

“Find the average number of stars for each state. Output the state name along with the corresponding average number of stars.”

Link to the question: https://platform.stratascratch.com/coding/10052-find-the-average-number-of-stars-for-each-state

Like in the previous questions, you have only one table at your disposal. Again, output only one column along with the aggregate values. Here you’ll have to use the AVG() function to get the solution. Also, the question doesn’t ask you to order data, so no need to use the ORDER BY clause.

Use the widget and hopefully you can get the output shown below it.

Your output should look like this:

Answer Output for SQL Query Interview Question from Yelp

SQL Query Interview Question #4: Find the Total Costs and Total Customers Acquired in Each Year

At Uber you could get a question like this:

“Find the total costs and total customers acquired in each year.
Output the year along with corresponding total money spent and total acquired customers.”

Link to the question: https://platform.stratascratch.com/coding/10009-find-the-total-costs-and-total-customers-acquired-in-each-year

This time you’ll be using the SUM() function two times: first to get the total costs and the second time to get the total customers. You need to show this data grouped on an annual level. No other calculations or ordering data is required.

The expected output has three columns and three rows:

Answer to SQL Query Interview Question from Uber

Try to write the solution.

SQL Query Interview Question #5: Average Number Of Points

The last question in this category is by ESPN.

“Find the average number of points earned per quarterback appearance in each year. Each row represents one appearance of one quarterback in one game. Output the year along with the corresponding average points. Sort records by the year in descending order.”

Link to the question: https://platform.stratascratch.com/coding/9965-average-number-of-points

This example asks you to use the AVG() aggregate function. Since you need to output the year and the quarterback’s appearance, you’ll also have to group by two rows, not only one. You’ll have to order the output by two rows, too. This time it’s in descending order, unlike the previous questions.

Here’s the partial output.

Answer to SQL Query Interview Question from ESPN

Do you think you can get it by using the hints we gave you?

Filtering Data and Conditional Statements

SQL Questions for Filtering Data and Conditional Statements

What we mean by filtering data is only the part of data that specifies set criteria. Even though using JOINs can also technically filter data, here we want to talk about the WHERE and HAVING clauses.

Conditional statements in PostgreSQL are represented through the CASE WHEN statements.

SQL Query Interview Question #6: Patrons Who Renewed Books

The City of San Francisco is interested in patrons and books.

“Find the number of patrons who renewed books less than 10 times in April 2015.”

Link to the question: https://platform.stratascratch.com/coding/9931-patrons-who-renewed-books

Here we have a table named library_usage with the columns:

library_usage

patron_type_codeint
patron_type_definitionvarchar
total_checkoutsint
total_renewalsint
age_rangevarchar
home_library_codevarchar
home_library_definitionvarchar
circulation_active_monthvarchar
circulation_active_yearfloat
notice_preference_codevarchar
notice_preference_definitionvarchar
provided_email_addressbool
year_patron_registeredint
outside_of_countybool
supervisor_districtfloat

And here’s how the (partial) data looks like:

Dataset for SQL Query Interview Questions from City of San Francisco

Now, to answer the question’s requirements, we’ll use something we already know. The COUNT() function, that is. We’ll use it to count rows which will equal to number of patrons. There’s only one table and it will appear in the FROM clause. The question doesn’t ask to output all patrons. No, the output must show patrons whose total renewals are between one and nine. These renewals all have to be in April 2015. To filter data in such way we’ll have to use three WHERE clauses:

SELECT count(*) AS n_patrons
FROM library_usage
WHERE total_renewals BETWEEN 1 AND 9
  AND circulation_active_month = 'April'
  AND circulation_active_year = '2015'

The output is one number:

Answer to SQL Query Interview Question from City of San Francisco

It means there are three patrons that renewed books less than ten times in April 2015.

SQL Query Interview Question #7: Find Out Search Details for Apartments Designed for a Sole-Person Stay

Airbnb is interested in knowing the answer to this question:

“Find the search details made by people who searched for apartments designed for a single-person stay.”

Link to the question: https://platform.stratascratch.com/coding/9615-find-out-search-details-for-apartments-designed-for-a-sole-person-stay

Here you have to output all the columns from the table. However, the rows have to be filtered. Use the WHERE clause to find the accommodation that is for only one person and that is classified as an apartment in the table.

And these are the first few rows of the output you should aim for.

Answer Output for SQL Query Interview Question from Airbnb

SQL Query Interview Question #8: Find Departments With Less Than 5 Employees

Amazon is interested in knowing the answer to this question:

“Find departments with less than 5 employees. Output the department along with the corresponding number of workers.”

Link to the question: https://platform.stratascratch.com/coding/9860-find-departments-with-less-than-5-employees

They give you the table worker:

worker

worker_idint
first_namevarchar
last_namevarchar
salaryint
joining_datedatetime
departmentvarchar

Make yourself familiar with the table’s data.

Dataset for SQL Query Interview Questions from Amazon

To answer the question, you’ll again have to filter data. This time you need to use HAVING instead of WHERE. They work the same in terms of syntax, but the main difference is WHERE is used before while HAVING is used after the GROUP BY clause. It’s also important to note that you need to use the COUNT() function two times: once in the SELECT statement and the second time in the HAVING clause.

Or translated to an SQL code:

SELECT department,
       count(worker_id) AS num_of_workers
FROM worker
GROUP BY department
HAVING count(worker_id) < 5

Run it to get the output we’re looking for.

Answer to SQL Query Interview Question from Amazon

There are three departments with less than five employees. The Admin Department has four employees, while Account Department and HR have two employees each.

SQL Query Interview Question #9: Departments With 5 Employees

Now it’s your turn to use the HAVING() clause and answer the Salesforce question:

“Find departments with at more than or equal 5 employees.”

Link to the question: https://platform.stratascratch.com/coding/9911-departments-with-5-employees

It’s not that different from the previous question. This time, you don’t need to show the number of employees, only the departments with five or more of them. It’s again the COUNT() function in the HAVING() clause.

Show us your solution here:

If you get it right, your solution will be this.

Answer to SQL Query Interview Question from Salesforce

SQL Query Interview Question #10: Make a Report Showing the Number of Survivors and Non-survivors by Passenger Class

This interesting question is asked by Google:

“Make a report showing the number of survivors and non-survivors by passenger class. Classes are categorized based on the pclass value as:
pclass = 1: first_class
pclass = 2: second_classs
pclass = 3: third_class
Output the number of survivors and non-survivors by each class.”

Link to the question: https://platform.stratascratch.com/coding/9881-make-a-report-showing-the-number-of-survivors-and-non-survivors-by-passenger-class

The table titanic has these columns:

titanic

passengeridint
survivedint
pclassint
namevarchar
sexvarchar
agefloat
sibspint
parchint
ticketvarchar
farefloat
cabinvarchar
embarkedvarchar

There’s quite a lot of data in it, so we’ll show only several rows.

Dataset for SQL Query Interview Questions from Google

This question does want us to filter data, in a way. It wants us to find certain data and allocate values to these data. It gives us a hint that we’ll have to use the CASE WHEN clause. We need to take the column pclass and check the values in it. If the value is 1, then it will be recorded as 1 in the new column first_class. If there’s value 2 in the column pclass, the value of 1 has to appear in the new column second_class. The same principle applies to the third class.

After you get this, you only need to the sum these values using the SUM() function. The data need to be grouped so that it shows survivors and non-survivors, which is shown in the column survived.

Finally, you’ll get this code:

SELECT survived,
       sum(CASE
               WHEN pclass = 1 THEN 1
               ELSE 0
           END) AS first_class,
       sum(CASE
               WHEN pclass = 2 THEN 1
               ELSE 0
           END) AS second_class,
       sum(CASE
               WHEN pclass = 3 THEN 1
               ELSE 0
           END) AS third_class
FROM titanic
GROUP BY survived

It results in the two-row output showing the survivors and non-survivors according to class.

Answer to SQL Query Interview Question from Google

There were 11 non-survivors in the first class, six in the second class, and 42 in the third class. Of the survivors, ten were traveling in the first class. The second and the third class had 12 and 19 survivors, respectively.

JOINs and UNIONs

SQL Questions for JOINs and UNIONs

The following topics we’ll cover here is joining tables and merging query outputs.

As you already know, the JOINs are used to combine data from two or more tables, thus allowing much more possibilities in querying data than when working with only one table.

We use UNION and UNION ALL to merge the outputs of two or more SELECT statements. For this merging to work, the outputs of the queries must have the same number of columns and their data types have to be of the same data type. The difference between them is that UNION ALL merges all the records, including duplicate, while UNION keeps unique records.

SQL Query Interview Question #11: Find the Team Division of Each Player

The interview question which we’ll use to show you how the JOINs work is the one by ESPN.

“Find the team division of each player. Output the player name along with the corresponding team division.”

Link to the question: https://platform.stratascratch.com/coding/9884-find-the-team-division-of-each-player

The data is divide into two tables.

The first one is college_football_teams

divisionvarchar
conferencevarchar
school_namevarchar
roster_urlvarchar
idint

with sample data shown here.

Dataset for SQL Query Interview Questions from ESPN

The second table is college_football_players

full_school_namevarchar
school_namevarchar
player_namevarchar
positionvarchar
heightint
weightint
yearvarchar
hometownvarchar
statevarchar
idint

You can see its partial data below.

Dataset 2 for SQL Query Interview Questions from ESPN

How do we approach this question? We select the column player_name from the second table and the column division from the first table. To get the data from these columns, we need to INNER JOIN the tables on the columns school_name from both tables. There’s nothing else to this solution.

SELECT players.player_name,
       teams.division
FROM college_football_teams teams
INNER JOIN college_football_players players ON teams.school_name = players.school_name
Answer to SQL Query Interview Questions from ESPN

Let’s try another one!

SQL Query Interview Question #12: Sales With Valid Promotion

Here’s a question by Meta/Facebook:

“To improve sales, the marketing department runs various types of promotions. The marketing manager would like to analyze the effectiveness of these promotional campaigns. In particular, what percentage of sales had a valid promotion applied? Only the promotions found in the facebook_promotions table are valid.”

Link to the question: https://platform.stratascratch.com/coding/2069-sales-with-valid-promotion

Here you’ll again have to JOIN two tables. This time it’s the LEFT JOIN, but it works the same way as the INNER JOIN syntax-wise. To get the percentage, you’ll have to use the COUNT() function both in the numerator and the denominator. Also, convert the result and multiply it by 100 to get the percentage with decimal points.

Here’s what your code needs to do.

Answer to SQL Query Interview Question from Meta Facebook

Write the solution in the widget.

SQL Query Interview Question #13: Transactions by Billing Method and Signup ID

Here’s a bit more complicated JOIN question by Noom.

“Get list of signups which have a transaction start date earlier than 10 months ago from March 2021. For all of those users get the average transaction value and group it by the billing cycle.

Your output should include the billing cycle, signup_id of the user, and average transaction amount. Sort your results by billing cycle in reverse alphabetical order and signup_id in ascending order.”

Link to the question: https://platform.stratascratch.com/coding/2031-transactions-by-billing-method-and-signup-id

The solution has to use the AVG() aggregate function, no doubt. Also, you’ll want to use two INNER JOINs here, because you need to output data from all three available tables. The question tells you to filter data according to start date. Calculate ten months before March 2021 and use the result in the WHERE clause. Also, you’ll need to group data by the billing cycle and signup_id. When you order data, you need to do it both ascendingly and descendingly.

Try to write your own solution and see if it returns the same few rows as the result below.

Answer to SQL Query Interview Question from Noom

SQL Query Interview Question #14: Make the Friends Network Symmetric

We didn’t talk about the UNION and UNION ALL, but this will change with the next two questions. The first one is by Google.

“Make the friends network symmetric. For example, if 0 and 1 are friends, have the output contain both 0 and 1 under 1 and 0 respectively.”

Link to the question: https://platform.stratascratch.com/coding/9813-make-the-friends-network-symmetric

The question wants you to only use the table google_friends_network and its two columns.

user_idint
friend_idint

We’re showing you only the first few rows.

Solving SQL Query Interview Question using UNION

This is a rather easy question to solve if you know how to use UNION. All you got to do is output the user ID and the friend ID with the first SELECT statement. The second SELECT statement will be the same, only the order of the columns in the output will be reversed. To get these two results in one output, you’d want to use the UNION operator like this:

SELECT user_id,
       friend_id
FROM google_friends_network
UNION
SELECT friend_id AS user_id,
       user_id AS friend_id
FROM google_friends_network

It should return the following result.

SQL Query Interview Questions Solution using UNION

The users 0 and 2, for example, appear both as users and friend, which is the point. The same is true for all other users.

SQL Query Interview Question #15: Find Employees in the HR Department and Output the Result With One Duplicate

Here we’ll show the question asked by Amazon.

“Find employees in the HR department and output the result with one duplicate. Output the first name and the department of employees.”

Link to the question: https://platform.stratascratch.com/coding/9858-find-employees-in-the-hr-department-and-output-the-result-with-one-duplicate

The approach to the solution should be similar to the example above. This time you’ll use UNION ALL instead of UNION. Why? Because the question asks you to return the duplicate results. Both SELECT statements that you’ll write should be exactly the same because they select two columns from the table. Also, you need only data from the HR department, so use that info in the WHERE clause.

Did you get this result?

Solving SQL Query Interview Question using UNION ALL

Subqueries and CTEs

SQL Interview Questions for Subqueries and CTEs

We’re building up your readiness for the SQL interview, so we’ll freely start using all the previous concepts. You should be comfortable with them by now.

The subqueries are the queries written in the other query. They usually appear in the SELECT, FROM, and WHERE.

The Common Table Expressions or CTEs are the tidier version of subqueries. They result in a table similar to the temporary table. This table isn’t stored anywhere and can be accessed only after the CTE is executed. One of the main benefits compared to the subqueries is that the CTEs are reusable and can be referenced in the coming SELECT statement like any other table.

SQL Query Interview Question #16: Successfully Sent Messages

The first question we’re going to show here is one by Meta/Facebook.

“Find the ratio of successfully received messages to sent messages.”

Link to the question: https://platform.stratascratch.com/coding/9777-successfully-sent-messages

The question gives you two tables to work with. The first one is facebook_messages_sent.

senderint
message_idint
textvarchar

Here’s its data:

Facebook SQL Question to find Successfully Sent Messages

The other table is facebook_messages_received.

receiverint
message_idint
textvarchar

Here’s its data, too:

Dataset for SQL Question to find Successfully Sent Messages

Now we have to use the subqueries. Both will be used in the same SELECT statement: one as the numberer the other one as the numerator, the other one as the denominator. Yes, we’re going to divide one subquery with the another one.

SELECT
  (SELECT CAST(COUNT(*) AS decimal)
   FROM facebook_messages_received)/
  (SELECT COUNT(*)
   FROM facebook_messages_sent)

One query will return the number of the received messages by using the COUNT() function. Its result will be:

Answer to SQL Question to find Successfully Sent Messages

The other subquery will also use the COUNT() function, but to return the number of the sent messages:

Answer 2 to SQL Question to find Successfully Sent Messages

Run together as the question solution and they will return this output.

Final Solution to SQL Question to find Successfully Sent Messages

Really, three divided by 5 is 0.6.

SQL Query Interview Question #17: Find the Most Profitable Company in the Financial Sector of the Entire World Along With Its Continent

If you want to work at Forbes, you should expect this question.

“Find the most profitable company from the financial sector. Output the result along with the continent.”

Link to the question: https://platform.stratascratch.com/coding/9663-find-the-most-profitable-company-in-the-financial-sector-of-the-entire-world-along-with-its-continent

To solve it, you’ll again need to use the subquery. This time, it’ll be a subquery in the WHERE clause. You should use it to get the highest profit in the financial sector. The other criteria in the outer query’s WHERE clause should also be the company’s sector.

Use the widget and try to get the following result.

Answer to SQL Query Interview Question from Forbes

SQL Query Interview Question #18: Top 10 Songs

Here’s an interview question by Spotify.

“Find the number of songs of each artist which were ranked among the top 10 over the years. Order the result based on the number of top 10 ranked songs in descending order.”

Link to the question: https://platform.stratascratch.com/coding/9743-top-10-songs

To solve this question, use the COUNT() function. It will get you the distinct songs by the artists. This time you’ll not be listing the table in the FROM clause, but you’ll write a subquery there. Use it to find artists and their songs that were ranked 10 or higher. In the main SELECT statement you’ll need to group and order data like this, at least partially.

Answer to SQL Query Interview Question from Spotify

Can you get the same result?

SQL Query Interview Question #19: Liking Score Rating

The question by Meta/Facebook is perfect to show you how the CTE works.

“Find how the number of `likes` are increasing by building a `like` score based on `like` propensities. A `like` propensity is defined as the probability of giving a like amongst all reactions, per friend (i.e., number of likes / number of all reactions).
Output the average propensity alongside the corresponding date and poster. Sort the result based on the liking score in descending order.
In `facebook_reactions` table `poster` is user who posted a content, `friend` is a user who saw the content and reacted. The `facebook_friends` table stores pairs of connected friends.“

Link to the question: https://platform.stratascratch.com/coding/9775-liking-score-rating

The table facebook_reactions has the columns:

facebook_reactions

posterint
friendint
reactionvarchar
date_dayint
post_idint

Here’s the sample of the data.

dataset for sql question to find liking score rating

Another table you’ll have to use is facebook_friends:

user1int
user2int

Below is the data.

dataset 2 for sql question to find liking score rating

What you got to do here to write a correct solution is write a CTE. You do that by using the keyword WITH, after which comes the CTE name, then follows the AS keyword that introduces the CTE definition. (The CTE’s SELECT statement, in other words).

In our case, the CTE is named p.

WITH p AS
  (SELECT SUM(CASE
                  WHEN reaction = 'like' THEN 1
                  ELSE 0
              END)/COUNT(*)::decimal AS prop,
          friend
   FROM facebook_reactions
   GROUP BY 2)

We use this CTE to calculate the propensity by dividing the number of likes with the number of all reactions. If you run only this SELECT statement, you’ll get this:

Output for sql question to find liking score rating

It’s a propensity per friend.

To get the average propensity, along with the date and poster, you’ll need to join the CTE with the table facebook_reactions, and use the AVG() function. This second part of the solution is:

SELECT date_day,
       poster,
       avg(prop)
FROM facebook_reactions f
JOIN p ON f.friend= p.friend
GROUP BY 1,
         2
ORDER BY 3 DESC

It can’t be run separately from the CTE because it references the CTE and its result can’t be, you’ll remember that, used unless the query has been run. So the complete solution is:

WITH p AS
  (SELECT SUM(CASE
                  WHEN reaction = 'like' THEN 1
                  ELSE 0
              END)/COUNt(*)::decimal AS prop,
          friend
   FROM facebook_reactions
   GROUP BY 2)
SELECT date_day,
       poster,
       avg(prop)
FROM facebook_reactions f
JOIN p ON f.friend= p.friend
GROUP BY 1,
         2
ORDER BY 3 DESC

And here’s the output.

Final output for sql question to find liking score rating

SQL Query Interview Question #20: Common Friends Friend

This is a hard question by Google, but give it a try.

“Find the number of a user's friends' friend who are also the user's friend. Output the user id along with the count.”

Link to the question: https://platform.stratascratch.com/coding/9821-common-friends-friend

To solve this question, again use the CTE. This time, it will consist of two SELECT statements and you’ll merge their results using the UNION. That way you get users and their friend, something that we already did in one of the questions above.

Once you write the CTE, use the COUNT() function to find the number of friends. This will be a bit more complex query that will use subquery in the FROM clause. The subquery will reference the CTE three times, which means the CTE will be self-joined and then grouped by the user ID.

Here’s what you get if the solution is correct.

Hard SQL Query Interview Question from Google

Window Functions

Window Functions SQL Query Questions

The SQL window functions are a valuable SQL feature most useful when you want to show the aggregated and the non-aggregated data at the same time. They share some similarities with the aggregate functions. However, the SQL aggregate functions lose the individual rows while the window functions don’t.

SQL Query Interview Question #21: Most Profitable Companies

We’ll use the Forbes question to show you the window functions.

“Find the 3 most profitable companies in the entire world. Output the result along with the corresponding company name. Sort the result based on profits in descending order.”

Link to the question: https://platform.stratascratch.com/coding/9680-most-profitable-companies

The table forbes_global_2010_2014 consists of the following columns:

forbes_global_2010_2014

companyvarchar
sectorvarchar
industryvarchar
continentvarchar
countryvarchar
marketvaluefloat
salesfloat
profitsfloat
assetsfloat
rankint
forbeswebpagevarchar

The data is:

Dataset for SQL Query Interview Question from Forbes

Here’s how we’re going to approach the solution. We’ll write two subqueries: one in the main query’s FROM clause and the other one in the subquery’s. Their purpose is to find the companies and their profit, then rank them according to that. Here’s how these two subqueries look.

SELECT *,
       dense_rank() OVER (
                          ORDER BY profit DESC) AS rank
FROM
  (SELECT company,
          sum(profits) AS profit
   FROM forbes_global_2010_2014
   GROUP BY company) sq

Even though the first SELECT statement now looks like the regular SELECT statement, it is a subquery, though. You’ll realize that once we write the main SELECT statement.

Notice there’s a window function DENSE_RANK(). This and other window functions are introduced by the OVER() clause. This clause can remain empty, but it also can contain PARTITION BY and/or the ORDER BY clause. The ORDER BY here tells the window function to rank according to profit from the highest to the lowest one.

This the subqueries’ partial output.

Dataset 2 for SQL Query Interview Question from Forbes

We got the companies rank and their profit. Now we have to put these subqueries within the main query that will output the company and its profit, and then use the WHERE clause to show only the companies with the ranking 3 or higher.

The complete solution to this question is:

SELECT company,
       profit
FROM
  (SELECT *,
          dense_rank() OVER (
                             ORDER BY profit DESC) AS rank
   FROM
     (SELECT company,
             sum(profits) AS profit
      FROM forbes_global_2010_2014
      GROUP BY company) sq) sq2
WHERE rank <=3

It should give you the output shown below.

Dataset 3 for SQL Query Interview Question from Forbes

This tells us the three most profitable companies are ICBC, Gazprom, and Apple.

SQL Query Interview Question #22: Lowest Priced Orders

Another example where you’ll have to use the window functions is this question by Amazon.

“Find the lowest order cost of each customer. Output the customer id along with the first name and the lowest order price.”

Link to the question: https://platform.stratascratch.com/coding/9912-lowest-priced-orders

To find the lowest order, use the FIRST_VALUE() window function. It will give you the first value in the set of values. Also, you’ll have to use both the PARTITION BY and the ORDER BY clauses. To get all data you need you’ll have to join the tables you have at your disposal.

If you got everything right, your output will be the same.

Output for sql question to lowest priced orders

SQL Query Interview Question #23: Fastest Hometowns

Deloitte asks you the following:

“Find the hometowns with the top 3 average net times. Output the hometowns and their average net time. In case there are ties in net time, return all unique hometowns.”

Link to the question: https://platform.stratascratch.com/coding/2066-fastest-hometowns

Write the solution so that you use the subquery. Use it to find the hometown, average net time and the rank according to it. To achieve that, use the AVG() and DENSE_RANK() window functions. Embed this subquery within the query that will filter only data with the top 3 rank.

Try to get this output.

Answer to SQL Query Interview Question from Deloitte

SQL Query Interview Question #24: Days at Number One

Things will get more complicated with this and the next question. Spotify gives you these instructions:

“You have a table with US rankings of tracks and another table with worldwide rankings of tracks.
Find the number of days a US track has been in the 1st position for both the US and worldwide rankings. Output the track name and the number of days in the 1st position. Order your output alphabetically by track name.”

Link to the question: https://platform.stratascratch.com/coding/10173-days-at-number-one

Use the subquery in the FROM clause to get the track name and the number of days at the 1st position. You’ll have to do it by using the SUM() function and the CASE WHEN statement inside it. Also, this SUM() function will not be the ordinary aggregate one, but the window function. Join the tables you have and filter data to only get those where the position is 1.

The main query will then use this subquery and the MAX() function to get the result:

SQL Question for Days At Number One

SQL Query Interview Question #25: Most Expensive and Cheapest Wine

Here’s the final interview question for practicing the window functions. The question is by Wine Magazine.

“Find the cheapest and the most expensive variety in each region. Output the region along with the corresponding most expensive and the cheapest variety.”

Link to the question: https://platform.stratascratch.com/coding/10041-most-expensive-and-cheapest-wine

Approach writing a code in the following way. First write two CTEs. The first CTE will have two SELECT statements and they will show the wine variety, region and price. Combine their outputs into one.

The second CTE will reference the first one to rank wines using the ROW_NUMBER() window function and getting the rank from the most expensive to the cheapest wine and vice versa.

After that comes the SELECT statement that references the second CTE. To get the desired output, you have to use the CASE WHEN statement in two MAX() functions: one to get the variety of the most expensive wine, the other to get the cheapest wine’s variety. You’re interested only in the cheapest and the most expensive wine, so you’ll have to filter data using the WHERE clause.

If you get the below result, then you got everything right.

SQL Query Interview Question to find Most Expensive and Cheapest Wine

Text & Date Manipulation

Text & Date Manipulation SQL Questions

The last topic we’ll cover here is different ways of working with the date and text data types.

SQL Query Interview Question #26: Combine the First and Last Names of Workers

One of the most common things you can be asked to do when working with text data is to concatenate two or more columns from the table. Here’s what exactly Amazon wants you to do.

“Combine the first and last names of workers with a space inbetween.”

Link to the question: https://platform.stratascratch.com/coding/9834-combine-the-first-and-last-names-of-workers

The table at your disposal is worker.

worker

worker_idint
first_namevarchar
last_namevarchar
salaryint
joining_datedatetime
departmentvarchar

Have a look at the data itself.

SQL Interview Question to Combine the First and Last Names of Workers

You see they want you to put the employee’s first and last name in one column. Here’s how you do it:

SELECT first_name || ' ' || last_name AS full_name
FROM worker

Select the employee’s first name, then follow it up with two vertical lines ||. What follows after that will be concatenated with the first name. We want the blank space to come after the first name, so we put blank space in the single quotation marks. After that again comes the sign for concatenation, and then the employee’s last name.

This will give their names in the new column full_name.

Answer to SQL Question to Combine the First and Last Names of Workers

SQL Query Interview Question #27: Print the First Three Characters of the First Name

We’re staying with Amazon and their question that again give you the same table as the question above.

“Print the first three characters of the first name.”

Link to the question: https://platform.stratascratch.com/coding/9828-print-the-first-three-characters-of-the-first-name

This question is easy to solve if you know how the SUBSTRING() function works. Use it to get the following result:

SQL Question to Print the First Three Characters

SQL Query Interview Question #28: Count the Number of Words per Row in Both Words Lists

Here’s one Google question that’s more complicated.

“Count the number of words per row in both words lists.”

Link to the question: https://platform.stratascratch.com/coding/9812-count-the-number-of-words-per-row-in-both-words-lists

Use the LENGTH() and the REPLACE() function to get the answer. Remember, LENGTH() is used to count the number of characters in string. REPLACE() searches one character in a string, and replaces it with another.

Here’s what you need to output:

SQL Question to Count the Number of Words per Row

SQL Query Interview Question #29: Users Activity per Month Day

This question by Meta/Facebook will test your knowledge on working with dates.

“Return a distribution of users activity per day of the month.”

Link to the question: https://platform.stratascratch.com/coding/2006-users-activity-per-month-day

You’re given the facebook_posts table.

facebook_posts

post_idint
posterint
post_textvarchar
post_keywordsvarchar
post_datedatetime

Here’s the data, too.

Facebook SQL Question to find Users Activity Per Month Day

How do you solve this question? The trick here is to use the DATE_PART() function. It will get you the day from the column post_date. The COUNT() function is there to find the number of posts. Also, convert the data type to date. Finally, group the result by the day and you get the correct solution.

SELECT date_part('day', post_date::date),
       count(*)
FROM facebook_posts
GROUP BY date_part('day', post_date::date)

Its output is here.

Solution Output for Facebook SQL Question

SQL Query Interview Question #30: Find All Workers Who Joined on February 2014

We’ll finish this off with the question by Amazon. It asks you:

“Find all workers who joined on February 2014.”

Link to the question: https://platform.stratascratch.com/coding/9844-find-all-workers-who-joined-on-february-2014

Here you’ll need to use the EXTRACT function to get the year and month for the data available and do it in the WHERE clause. The data also has to be cast to a date type.

Here’s the output:

Amazon SQL Question to Find All Workers Who Joined on February 2014

And the answer? Write it in the widget and let’s see what’ll happen.

Conclusion

With covering 30 questions we gave you a thorough understanding of the SQL coding interview questions. These questions cover six major topics you have to be skilled in.

They cover data aggregation, grouping, ordering, filtering, and using the conditional statements. To combine data there are JOINs and UNION. As the queries get more complex, you’ll start using subqueries, CTEs, and the window functions. All that has also include the knowledge on working with text and dates.

It’s not enough to know all these SQL concepts. To score more points you should be able to structure your SQL code well when providing the answer to the interview question.

Top 30 SQL Query Interview Questions


Become a data expert. Subscribe to our newsletter.