Amazon Sql Interview Questions Quora

The Amazon interview process is notoriously challenging, featuring multiple SQL rounds for almost al Data Analyst, Data Science, Data Engineering, and Business Intelligence roles. And while the work-life balance at Amazon isnt to die for, and the Amazon perks lack severely compared to other FAANG companies because Bezos is a notorious cheapskate, heres the silver lining: the compensation IS good at Amazon.

Real good, once you realize how much of your salary comes in Amazon stock, which has appreciated greatly (and will continue to appreciate until Amazon is the only store in the universe).

Thats why its worth your time to practice these 6 real Amazon SQL interview questions so you can Ace the SQL interview and land your dream job at Amazon where youll crush local small business one data-driven insight at a time!

What’s the difference between and ?

Essentially is to SELECT what 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 clause.

6 Real Amazon SQL Interview Questions

Given the reviews table, write a 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.

review_id user_id submit_date product_id stars
6171 123 06/08/2022 00:00:00 50001 4
7802 265 06/10/2022 00:00:00 69852 4
5293 362 06/18/2022 00:00:00 50001 3
6352 192 07/26/2022 00:00:00 69852 3
4517 981 07/05/2022 00:00:00 69852 2
mth product avg_stars
6 50001 3.50
6 69852 4.00
7 69852 2.50

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

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

There is a simple function to extract month from a date. Heres the syntax:

You can look at this page for more explanation on the 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 and . Please refer [1] & [2] for some reading on the functions.


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

  • SELECT fields instead of using
  • 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 Data Analysts and Data Scientists, Amazon expects people interviewing for Data Engineering and Business Intelligence roles to know how databases work internally, and best practices for database design. If you arent familiar with these concepts, check out my article on how to prep for database design interviews.

    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.

    Your given an table:


  • 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 :

    order_id customer_id order_datetime item_id order_quantity
    O-001 42489 2023-06-15 04:35:22 C004 3
    O-005 11733 2023-01-12 11:48:35 C005 1
    O-005 11733 2023-01-12 11:48:35 C008 1
    O-006 83167 2023-01-16 02:52:07 C012 2

    You are also given an table:


    Here’s some sample data from :

    item_id item_category
    C004 Books
    C005 Books
    C006 Apparel
    C007 Electronics
    C008 Electronics
  • How many units were ordered yesterday? Hint: Yesterday’s date be found via the PostgreSQL snippet
  • 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 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 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.

    Amazon SQL Interview Questions & Answers

    Related Posts

    Leave a Reply

    Your email address will not be published. Required fields are marked *