Amazon, Business Analyst Interview Experience

Summary:
Just got an offer for a Business Analyst role at Amazon and feel super excited! The technical assessments were pretty straightforward. I found the Amazon SQL on LC and buying FAANGTutors.com Amazon SQL prep guide super helpful.

Interview Experience:

• Phone interview: 10 mins intro + 15 mins LP + 25 mins for SQL Live coding exercise + 10 mins for your questions. Interviewer also asked if I’d like to do Python questions.
• Phone interview: 60-minutes focused on leadership principles
• Final round: 4 interviewers, 2 Sr BA’s, 1 DS, 1 hiring manager. Primarily LP and behavioral.

Questions:

• Give me the count of employees?
• Give me an avg salary by department?
• Give me a list of buildings where there are no employees in them?

Table: employee
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| employee_id | int |
| first_name | var |
| last_name | var |
| manager_id | int |
| salary | int |
| department | var |
| start_date | date |
| building_id | int |
+-------------+---------+

Table: building
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| building_id | int |
| building_name| int |
+--------------+---------+

**My solutions: **

Question 1:
SELECT COUNT(DISTINCT employee_id)
FROM employee

Question 2:

SELECT department ,
AVG(salary) AS avg_salary
FROM employee
GROUP BY 1

Question 3:
SELECT building_name ,
building_id
FROM building b
LEFT JOIN employee e ON e.building_id = b.building_id
WHERE employee_id IS NULL