6 REAL Amazon SQL Interview Questions (Updated 2024)

The Amazon interview process is notoriously challenging, featuring multiple SQL rounds for most Business Analyst, Data Analyst, Data Science, Data Engineering, and Business Intelligence Engineer (BIE) roles. And while the work-life balance at Amazon isn't to die for, and they've been doing layoffs in 2024, and the perks are lacking compared to other FAANG companies, here's the silver lining: the compensation IS good at Amazon.

So, to help you get that Jeff Bezos money, here's 6 real Amazon SQL interview questions to practice so you can Ace the SQL interview!

6 Real Amazon SQL Interview Questions

1. Write a SQL query to get the average review ratings for every product every month.

In a recent Amazon data analyst interview, the candidate was given the reviews table, and asked to write a SQL query to get the average stars for each product every month.

The output should include the month in numerical value, product id, and average star rating rounded to two decimal places. Sort the output based on month followed by the product id.

reviews Example Input:

Example Output:

Before peaking at the solution, you can try this real Amazon Data Analyst SQL interview question online in our interactive SQL code editor:

Amazon Review Ratings Solution:

As we can see, there is no month column in the reviews table. First, we have to extract the month from the submit_date column.

There is a simple function to extract month from a date. Here's the syntax: EXTRACT(MONTH from column_name)

You can look at this page for more explanation on the EXTRACT function.

After extracting the month in numerical values, get the average of the star ratings and round them to two decimal places. It can be achieved using the functions AVG() and ROUND(). If you're a bit rusty on how to do math in SQL, try this interactive tutorial on ROUND/FLOOR/CEIL/ABS.

Solution:

SELECT 
  EXTRACT(MONTH FROM submit_date) AS mth,
  product_id,
  ROUND(AVG(stars), 2) AS avg_stars
FROM reviews
GROUP BY EXTRACT(MONTH FROM submit_date), product_id
ORDER BY mth, product_id;

2. Amazon databases are HUGE. How do you optimize a slow SQL query?

• SELECT fields instead of using SELECT *
• Avoid SELECT DISTINCT
• Create joins with INNER JOIN (not WHERE)
• Avoid JOINs in general (maybe try de-normalization)
• Add indexes to your database
• Examine the SQL query execution plan

Note: the interviewer will likely push you for more detail, or ask you about a real example about a time you had to make one of these optimizations yourself.

While this question might be out-of-scope for Business Analysts, Data Analysts, Data Scientists, Amazon expects people interviewing for Data Engineering and Business Intelligence Engineering (BIE) roles to know how databases work internally, and best practices for database design.

That's because when operating on massive Amazon/AWS scale, they expect their Data Engineers/SWEs/BIEs to squeeze out every last performance improvement.

If you aren't familiar with these SQL optimization concepts, check out my article on how to prep for database design interviews and this guide on SQL optimization from ThoughtSpot.

3. What are SQL constraints, and what are example constraints?

Constraints are simply rules for what data goes into your database. Some SQL constraints are:

• NOT NULL - Stops NULL value from being inserted
• UNIQUE - Ensures unique values are inserted
• INDEX - Speeds up querying based on optimizing for a specific column that's often used to lookup records.
• PRIMARY KEY - Uniquely identifies each record
• FOREIGN KEY - Ensures referential integrity from on record to another in a different table

4. Write a SQL query to find the highest-grossing items.

Assume you are given the table containing information on Amazon customers and their spending on products in various categories. Identify the top two highest-grossing products within each category in 2022. Output the category, product, and total spend.

product_spend Example Input:

Example Output:

Before reading the solution, why don't try the Highest-Grossing Items Amazon SQL Interview Question yourself?

Amazon Highest Grossing SQL Query Solution

To find the highest-grossing products, we must find the total spend by category and product. Note that we must filter by transactions in 2022.

SELECT 
  category, 
  product, 
  SUM(spend) AS total_spend 
FROM product_spend 
WHERE transaction_date >= '2022-01-01' 
  AND transaction_date <= '2022-12-31' 
GROUP BY category, product;

The output represents the total spend by category (electronics, appliance) and product.

Then, we reuse the query as a CTE or subquery (in this case, we are using a CTE) and utilize the RANK window function to calculate the ranking by total spend, partition by category and order by the total spend in descending order.

Read more about SQL Window Functions.

WITH product_category_spend 
AS (
-- Insert query above)

SELECT 
  *, 
  RANK() OVER (
    PARTITION BY category 
    ORDER BY total_spend DESC) AS ranking 
FROM product_category_spend;

Finally, we use this result and filter for a rank less than or equal to 2 as the question asks for top two highest-grossing products only.

WITH product_category_spend AS (
SELECT 
  category, 
  product, 
  SUM(spend) AS total_spend 
FROM product_spend 
WHERE transaction_date >= '2022-01-01' 
  AND transaction_date <= '2022-12-31' 
GROUP BY category, product
),
top_spend AS (
SELECT *, 
  RANK() OVER (
    PARTITION BY category 
    ORDER BY total_spend DESC) AS ranking 
FROM product_category_spend)

SELECT category, product, total_spend 
FROM top_spend 
WHERE ranking <= 2 
ORDER BY category, ranking;

5. What's the difference between RANK() and DENSE_RANK()?

Essentially RANK is to SELECT what dense_rank() is to SELECT DISTINCT.

RANK() gives you the ranking within your ordered partition. Ties have the same rank, with the next ranking(s) skipped. So, if you have 4 items at rank 2, the next rank listed would be ranked 6.

DENSE_RANK() also ranks within your ordered partition, BUT the ranks are consecutive. This means no ranks are skipped if there are ranks with multiple items, and the rank order depends on your ORDER BY clause.

6. Amazon Orders SQL Technical Assessment

This question about Amazon orders comes from a real Amazon Data Analyst SQL assessment. It’s a multi-part SQL question, similar to how take-home SQL challenges are structured, and asks increasingly more complex questions about the amazon orders.

Amazon Orders Data

Your given an orders table:

ORDERS

• order_id (composite primary key)
• customer_id (integer)
• order_datetime (timestamp)
• item_id (composite primary key)
• order_quantity (integer)

Here’s some sample data from orders:

You are also given an items table:

ITEMS

• item_id (pimary_key)
• item_category (string)

Here’s some sample data from items:

Amazon SQL Assessment Questions

• How many units were ordered yesterday? Hint: Yesterday’s date be found via the PostgreSQL snippet SELECT current_date - INTEGER '1' AS yesterday_date
• In the last 7 days (including today), how many units were ordered in each category? Hint: You need to consider ALL categories, even those with zero orders!
• Write a query to get the earliest order_id for all customer for each date they placed an order. Hint: customers can place multiple orders on a single day!
• Write a query to find the second earliest order_id for each customer for each date they placed two or more orders.

We’ve left the answers to this Amazon take-home challenge to the reader, but you can find a similar multi-part SQL assessment from CVS Health on Pharmacy Analytics which comes with full solutions.

How To Prep for Amazon SQL Interviews

Besides solving the Amazon SQL interview questions above, we recommend practicing the bigger list of SQL interview questions from comparable companies like Facebook and Google.

You can also learn what SQL interviews generally cover, and how to best prepare for them in our 6,000 word SQL interview guide.

Amazon Business Analyst SQL Interview Tips

While Amazon Business Analyst interviews ask easier SQL questions than Data Engineering and BIE interviews, things can still be pretty rough, especially if you're more used to using Excel all day.

As such, we recommend Business Analysts master these simpler SQL topics before an Amazon SQL interview:

• filtering data with WHERE
• aggregate functions like SUM,AVG, COUNT
• using GROUP BY to make summary stats
• having a basic understanding of SQL joins

If these Amazon questions look too easy, then try some more advanced sql interview questions for a real challenge.

What Else Do Amazon Data Science Interviews Cover

For data roles like data science and data analytics, Amazon interviews also ask statistics interview questions, Machine Learning interview questions, product-sense interview questions, and of course coding interview questions:

Make sure to study up on your stats interview questions as well, as these at vital basics expected from every Data Scientist.

I'm a bit biased, but I also recommend the book Ace the Data Science Interview because it has multiple Amazon Data Science Interview questions with solutions in it.

And if you're looking for a one-page SQL Interview Cheat Sheet that covers everything you need to know for the interview check out this free one from DataLemur!

You should also study the Amazon leadership principles to pass the tricky bar-raiser rounds. For a deep dive into the Amazon 16 leadership principles, along with potential behavioral questions you'll get at Amazon check out our Amazon Behavioral Interview Guide.

You can also watch my mock Amazon behavioral interview video for help with the behavioral aspect!

Also Amazon will be asking more than just SQL questions, read up and prepare for the Python portion with the 9 best books on Python for Data Scientists.