Amazon SQL Interview Questions

Are you preparing for an interview at Amazon? Take a look at these interview questions and check your professional SQL knowledge!

Interview preparation for big companies such as Amazon can be overwhelming, but you can always practice beforehand and strengthen your SQL skills! In this article, we will go over Amazon SQL interview questions and help you prepare for your next big interview. Looking to master all of SQL and ace any task? Our complete SQL from A to Z track, where we cover every corner of SQL with more than 850 exercises, will help you reach your goal.

If you’re looking for more SQL job interview questions, check out our article Top 100+ SQL Interview Questions and Practice Exercises. In it, we’ve collected many other SQL exercises to help you prepare for your next interview.

How Does an Amazon SQL Interview Work?

You can apply to a position at an Amazon at amazon.jobs and use the search function to find all the jobs related to SQL. You can also search for related keywords like “database”, “data science”, or “analyst” to find similar positions.

If your application is successful and you have a relevant CV, a recruiter will reach out to you for a quick phone conversation. This first call will most probably not be technical; it will focus on determining whether you are a good culture fit for the company. Once you pass that screening, the technical interview starts.

Amazon’s technical interviewers will ask you questions related to SQL and data as well as other questions specific to the position you have applied for. Amazon’s SQL interviews are known for first covering the basics of syntax and then diving deep into complex JOINs and window functions. You can practice these topics with our SQL JOINs course and our extensive Window Functions course.

In this article, we will focus on practicing JOINs and window functions. If you want to refresh your knowledge on the basics of SQL, check out our list of common SQL interview questions.

Get to Know the Data

For this practice set, we will use two simple tables, salesperson and product. Let’s examine each of them.

The salesperson table stores information about all the company’s sales reps. Each one is identified with an id (stored as an INT datatype), and has a name (TEXT), a salary (NUMERIC), and an optional manager_id (INT). When it’s present, the manager ID points to the id of another sales rep that oversees the current salesperson.

The product table stores information about products that the salespeople sell. Each product is identified by its id (INT) and has a name (TEXT), price (NUMERIC), category (TEXT), and a mandatory salesperson_id (INT). This last field points to the id of the sales rep that sells the product.

Now that we’ve looked at the data, let’s get started on the exercises. Having our free SQL cheat sheets might be a good idea. Our SQL JOINs Cheat Sheet and  Window Functions Cheat Sheet will help you if you need a quick reminder about some command’s syntax.

Amazon SQL Interview Questions, Answers, and Explanations

1.    High-Earning Sales Reps

Task: Find all salespeople whose salary is greater than that of their managers.

Answer:

SELECT e1.name
FROM salesperson e1
JOIN salesperson e2
  ON e1.manager_id = e2.id
  AND e2.salary < e1.salary;

Explanation:

To pair each salesperson with their manager, we need to use a self-join - i.e. we need to join the table with itself. For a sales rep who has a manager, joining on the condition of salesperson1.id = salesperson2.manager_id will give us pairs of reps and their managers. There is no need for a LEFT JOIN here, as we are specifically looking only for salespeople that have a manager.

You can add the filtering condition (manager.salary < salesperson.salary) either in a WHERE clause or as an extra condition in the JOIN clause (creating a non-equi join). A non-equi join can be a good demonstration of your JOIN skills, but here the result is the same.

If you want more JOIN practice for your next interview, check out our SQL JOINs course, which will guide you through all the JOIN types and how to best use them.

2.    More Expensive Products

Task: For each product, count the number of products that are more expensive than it.

Answer:

SELECT
  p1.name,
  COUNT(p2.name) AS num_products
FROM product p1
LEFT JOIN product p2
  ON p1.price < p2.price
GROUP BY p1.name;

Explanation:

This task can be easily solved with either a self-join or a subquery. Since Amazon interviews tend to lean heavily into complicated JOINs (and because JOIN queries tend to run faster than subqueries), we will use another self-join.

To skip filtering after the join, we put the condition product1.price < product2.price in the join itself, inside the ON clause. A LEFT JOIN ensures we include the most expensive product. The most expensive product has no corresponding products to join with, so it would be excluded in a regular JOIN.

3.    Products Over a Rep's Salary

Task: For each sales rep, show how many products they sell that are priced higher than their salary. Include all salespeople in the result, even if they don’t sell such products.

Answer:

SELECT
  s.id,
  s.name,
  s.salary,
  s.manager_id,
  COUNT(p.name) AS expensive_products
FROM salesperson s
LEFT JOIN product p
  ON s.id = p.salesperson_id
  AND p.price > s.salary
GROUP BY
  s.id,
  s.name,
  s.salary,
  s.manager_id;

Explanation:

Join product and salesperson. To only count the products that cost more than the sales rep’s salary, use the filtering condition p.price > s.salary. You might want to place it in the WHERE clause after the JOIN, but most reps don’t have such expensive products and the filtering condition will get rid of all of them. Instead, put this condition as an additional one when joining the two tables and use a LEFT JOIN.

If a salesperson does not have any products that cost more than their salary, they will still be kept in the final result by the LEFT JOIN. Their expensive_products count will have a value of 0.

If you want to learn more about when to use non-equi JOINs, check out these detailed non-equi JOIN examples.

4.    Product Price by Category

Task: For each product, find how its price compares to the average price of other products in the category. Show the name and price of the product, together with the average price of all other products in the same category.

Answer:

SELECT
  p1.name,
  p1.price,
  AVG(p2.price) AS avg_price_in_category
FROM product p1
JOIN product p2
  ON p1.category = p2.category
  AND p1.id != p2.id
GROUP BY
  p1.name,
  p1.price;

Explanation:

To calculate the average price of all other products in the same category, you have to create pairs like this: product - another product of the same category. To do it, self-join the product table on the category of the product. This will include rows where the product is joined with itself, so an extra condition needs to be added in the JOIN: product1.name != product2.name. (This could also go in the WHERE clause.)

Now with all the product pairs complete, we can average out the price of the second product in the pair and group by the first product. This will get the desired result.

5.    Average Salesperson Salary

Task: Show all the sales rep’s data as well as a column with the average salary of all reps and the difference between this salesperson’s salary and the average_salary for all reps.

Answer:

SELECT 
  id,
  name,
  salary,
  manager_id,
  AVG(salary) OVER() AS average_salary,
  salary - AVG(salary) OVER() AS salary_difference
FROM salesperson;

Explanation:

To get the average salary for all salespeople, we will use the AVG() window function. It works the same way as the AVG() aggregate function, but to make it a window function we use OVER() instead of GROUP BY.

Use an empty OVER() clause to get the average value of salary for all the salespeople. Name the column average_salary for clarity. Subtract the salary of each sales rep (which is different for each row) from average_salary (which is the same for each row) to get the salary difference for each rep. Name this salary_difference.

If you would like more window functions practice, check out our Window Functions course. It offers over 200 interactive exercises so you can get to know window functions in detail!

6.    Relative Product Prices

Task: For each product, show the ratio between its price and the price of the most expensive product in that category.

Answer:

SELECT
  name,
  price,
  price / MAX(price) OVER(PARTITION BY category) AS price_ratio
FROM product;

Explanation:

To calculate the ratio of the current product’s price to the most expensive product price in the same category, we need to use this formula: ratio = price / (max price from the same category).

We cannot use GROUP BY here, as it collapses the individual rows; instead, we use the MAX() window function. Unlike the last exercise, here we will explicitly specify the partition. We want to get a separate result for each category, so the syntax we will use will be: PARTITION BY category. Place it in the OVER() clause, which comes just after the MAX() window function.

7.    Product Price Ranks Within Categories

Task: Show how each product’s price ranks within its category. Show the name, price, and category of each product together with the price rank. The most expensive product in its category should rank first and the cheapest product should rank last.

Answer:

SELECT
  name,
  price,
  category,
  ROW_NUMBER() OVER(PARTITION BY category ORDER BY price DESC) AS category_rank
FROM product

Explanation:

To create a ranking for each row within its category, we need to use ranking window functions. There are a few options: RANK(), DENSE_RANK(), or ROW_NUMBER(). While they might give different results in some cases, in this instance any of them will work. You can find out more about the differences between these three functions in our overview of SQL ranking functions.

For this example, we will use ROW_NUMBER(). It will return each row’s number within its category, without skipping or repeating numbers when products share the same rank.

We use ROW_NUMBER() together with OVER() to specify the size of the window and the order in which the rows are ranked. PARTITION BY category sets the window to all products of the same category as the current row. This ensures that only products in the same category are considered for calculating the rank. Then ORDER BY price DESC sets the ranking order (products with larger prices will be ranked higher).

8.    Managers’ Highest-Paid Reps

Task: Among sales reps that report to a manager, show the ones that are paid the most. In other words: for every manager with subordinates, show their highest paid subordinate but not the manager themselves.

Answer:

WITH salesperson_with_highest_salary as (
  SELECT
    id,
    name,
    salary,
    manager_id,
    MAX(salary) OVER (PARTITION BY manager_id) AS highest_salary
  FROM salesperson
)
SELECT *
FROM salesperson_with_highest_salary
WHERE salary = highest_salary;

Explanation:

First, we will create an intermediate result: for each salesperson, we will find their ID, name, salary, manager ID, and the highest salary among their colleagues (i.e. reps who share the same manager). We use the MAX() window function together with OVER (PARTITION BY manager_id) to find the highest salary per group of reps with the same manager_id.

Note that we wrap the query in a common table expression (CTE). A CTE starts with the WITH keyword and a name. It gives this name to these intermediate results, which can be referenced later in the main query.

Here, the CTE is named salesperson_with_highest_salary. If you need a reminder on how CTEs work, check out our guide to common table expressions in SQL. For a more in-depth approach, try our Common Table Expressions course.

We only need to show sales reps that are the highest paid in their group. Now that you have the rep’s salary together with the highest_salary from the group, you can filter the salespeople in the outer query to show only the one(s) that have the same salary as the top salary from the group. If two or more reps in a group have the highest salary, all of them will be shown in the final result.

9.    Most Expensive Products by Category

Task: In each category, find the three highest-priced products. If several products have the same price, include all of them.

Answer:

WITH product_ranks AS (
  SELECT
  name,
  price,
  category,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank
  FROM Product
)
SELECT * 
FROM product_ranks
WHERE rank <= 3;

Explanation:

We use a window function inside of a CTE again. This time, we need to create an additional ranking for each row within its category. We need to use DENSE_RANK() for this; it will return all products with the three highest prices, even if some products have the same price.

Use PARTITION BY category to get a separate ranking within each category and ORDER BY price DESC to make sure that the higher priced products get the higher ranks. Wrap the query that finds the ranking for each row in a CTE so the ranking will be available when filtering the rows. Any row with the rank of 1, 2, or 3 is within the top three products in its category; use this as a filtering condition in the outer query to get the final result.

Ready for Your Amazon SQL Interview?

Nicely done! If you have successfully solved all the tasks in our Amazon SQL interview prep, you can be confident in your advanced SQL skills. Good luck with your next interview!

Hungry for more complex SQL interview questions to practice? Take a look at these 15 tricky interview questions that combine theoretical and practical SQL topics. And if you are looking for a wider range of practice exercises, our Advanced SQL Practice Track has over 350. Happy practicing!