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.
Solution:
Amazon databases are HUGE. How do you optimize a slow SQL query?
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:
ORDERS
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:
ITEMS
Here’s some sample data from :
| item_id | item_category |
|---|---|
| C004 | Books |
| C005 | Books |
| C006 | Apparel |
| C007 | Electronics |
| C008 | Electronics |
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.