Are you preparing for a technical assessment with Amazon? Well look no further we’ve researched and selected the top 3 most common SQL questions to know before your Amazon interview.
Question 1: Sales by Day of the week
Write a SQL query to report how many units in each category have been ordered on each day of the week. Return the result table ordered by category. Difficulty level: Hard
Solution
Explanation
This is a common SQL interview question where the interviewer wants to test your skill in creating a Pivot Table. This is one of the most used tools in data analysis to calculate, summarize, and analyze data.
Let’s see how to solve this problem.
Step 1: We need to join both the tables using a LEFT JOIN. IF you try to join using INNER JOIN then you will get the wrong answer as the Order table does not have orders related to the item_id 6.
Tip: You can practice company-specific SQL questions for your next technical interview on Big Tech Interviews
Step 2: We join the table and since we needed the day of the week, we used the DAYNAME function in MYSQL. Now, we have everything that is needed to build the Pivot Table.
To build the Pivot Table we need to use CASE statements in SQL.
Step 3: The final structure of the pivot table is almost complete. To get the final result all we have to do is group the data by category and apply the sum function to each of the CASE statements.
Question 2: The Most Frequently Ordered Products for Each Customer
Write a SQL query to find the most frequently ordered product(s) for each customer. The result table should have the product_id and product_name for each customer_id who ordered at least one order.
Difficulty level: Medium
Solution
Explanation
This is another commonly asked question. The goal of this question is to check your understanding of window functions in SQL. The questions ask you to find the most frequently ordered products for each customer.
Let’s see how to solve this problem.
Step 1: To solve this problem you have to first group the data by each customer and the product and count how many times each product was purchased.
Step 2: Now, the questions ask for the most frequently ordered product so we have to do ranking on this data. The product which gets bought most will get the rank of 1 and second most get the rank of 2 and so on. For this, we can use the DENSE_RANK() function. In the DENSE_RANK function, we need to partition the data by each customer and order the total in desc order.
Step 3: Now, in the final query all we have to do is select the result where rnk is 1.
Question 3: Average Salary – Department vs Company
Write a SQL query to report the comparison result (higher/lower/same) of the average salary of employees in a department to the company’s average salary.
Difficulty level: Hard
Solution
