We’ve analyzed over 50,000 interviews from pre-IPO to Fortune 500 companies at Big Tech Interviews (BTI) to curate an exclusive list of the latest SQL interview questions and solutions so you can ace your next interview!
You’re about to read the 10 latest Amazon SQL interview questions and solutions asked by Amazon (+AWS) for data science, data analyst, business intelligence engineer, data engineer, and business analyst positions.
Want to practice more SQL interview questions like this with on-demand support and a dynamic SQL playground? Learn more here!
Amazon/AWS SQL Interview Question 36: Salary by department
Prompt:Write a query to find the average salary by department. Round the result to 2 decimal places.
Table: employee
+------+----------+---------+----------+------+----+----------+-----------+
|emp_id|first_name|last_name|manager_id|salary|dept|start_date|building_id|
+------+----------+---------+----------+------+----+----------+-----------+
|1 |hector |bush |1 |40000 |ops |2018-03-05|1 |
|2 |kim |stewart |1 |31000 |eng |2019-04-05|2 |
|3 |peter |thompson |1 |31700 |ds |2019-02-05|3 |
|4 |thomas |henderson|1 |32000 |ops |2018-03-05|2 |
|5 |john |free |1 |35000 |eng |2019-03-08|3 |
|6 |tara |glad |5 |32000 |ds |2019-01-12|1 |
|7 |linda |bruss |5 |30000 |ops |2020-04-19|3 |
|8 |forest |gump |6 |29000 |eng |2021-04-19|1 |
|9 |luke |smith |7 |28000 |ds |2019-06-24|2 |
+------+----------+---------+----------+------+----+----------+-----------+
Output
+----------+------------------+
|department|avg_salary |
+----------+------------------+
|eng |31666.67 |
|ds |30566.67 |
|ops |34000.00 |
+----------+------------------+
Solution
select
dept,
round(avg(salary),2) as avg_salary
from employee
group by dept;
Amazon/AWS SQL Interview Question 40: Companies that have no sales
Prompt: Write a query to show the companies in the US that have no sales yet.
Table: orders +--------+-------+--------+ |order_id|item_id|quantity| +--------+-------+--------+ |1 |46 |646 | |2 |63 |343 | |3 |23 |987 | |4 |24 |234 | |5 |53 |854 | +--------+-------+--------+
Table: catalog
+---------+------------+---------------+----------+
| item_id | product_id | product_group | location |
+---------+------------+---------------+----------+
|46 |543 |A |US |
|63 |567 |B |US |
|23 |566 |B |MX |
|24 |563 |C |CA |
|53 |568 |D |US |
|64 |560 |E |US |
+---------+------------+---------------+----------+
Output
+-------+----------+-------------+--------+
|item_id|product_id|product_group|location|
+-------+----------+-------------+--------+
|64 |560 |E |US |
+-------+----------+-------------+--------+
Solution
select
c.*
from catalog c
left join orders o on c.item_id = o.item_id
where location = 'US'
and o.order_id is null;
Amazon/AWS SQL Interview Question 38: Employees who earn the second-highest in each department
Prompt: Write a query to find the list of employees who earn the second-highest in each department
Table: employee +------+----------+---------+----------+------+----+----------+-----------+ |emp_id|first_name|last_name|manager_id|salary|dept|start_date|building_id| +------+----------+---------+----------+------+----+----------+-----------+ |1 |hector |bush |1 |40000 |ops |2018-03-05|1 | |2 |kim |stewart |1 |31000 |eng |2019-04-05|2 | |3 |peter |thompson |1 |31700 |ds |2019-02-05|3 | |4 |thomas |henderson|1 |32000 |ops |2018-03-05|2 | |5 |john |free |1 |35000 |eng |2019-03-08|3 | |6 |tara |glad |5 |32000 |ds |2019-01-12|1 | |7 |linda |bruss |5 |30000 |ops |2020-04-19|3 | |8 |forest |gump |6 |29000 |eng |2021-04-19|1 | |9 |luke |smith |7 |28000 |ds |2019-06-24|2 | +------+----------+---------+----------+------+----+----------+-----------+
Table: building +-----------+--------------+ |building_id|building_name | +-----------+--------------+ |1 |ss | |2 |vs | |3 |ts | |4 |zs | +-----------+--------------+
Output
+-----------+----------+---------+----------+
|emp_id |first_name|last_name|dept |
+-----------+----------+---------+----------+
|3 |peter |thompson |ds |
|2 |kim |stewart |eng |
|4 |thomas |henderson|ops |
+-----------+----------+---------+----------+
Solution
with employee_rank as (
select
emp_id,
first_name,
last_name,
dept,
row_number() over (partition by dept order by salary desc, start_date asc) as rank_
from employee
)
select
emp_id,
first_name,
last_name,
dept
from employee_rank
where rank_ = 2;
Amazon/AWS SQL Interview Question 51: Bids by category in the last 7 by day of week
Prompt: Write a query to find how many bids have been completed in each category for each day of the week. Please order the results by day of week in ASC order.
Table: bids +------+-----------+--------------------+-------+--------------+ |bid_id|customer_id| order_datetime |item_id|order_quantity| +------+-----------+--------------------+-------+--------------+ |A-001 |32483 |2021-12-15 09:15:22 |B000 |3 | |A-002 |21456 |2022-01-10 09:28:35 |B001 |1 | |A-003 |21456 |2022-01-09 09:28:35 |B005 |1 | |A-004 |42491 |2022-01-16 02:52:07 |B008 |2 | |A-005 |42491 |2022-01-18 02:52:07 |B008 |2 | |A-006 |42491 |2022-01-18 02:52:07 |B008 |5 | |A-007 |21456 |2022-01-17 09:28:35 |B000 |1 | |A-008 |21456 |2022-01-17 10:28:35 |B008 |3 | |A-009 |21456 |2022-01-19 10:28:35 |B000 |2 | +------+-----------+--------------------+-------+--------------+
Table: items
+---------+---------------+
| item_id | item_category |
+---------+---------------+
|B000 |Outdoors |
|B001 |Outdoors |
|B002 |Outdoors |
|B003 |Kitchen |
|B004 |Kitchen |
|B005 |Kitchen |
|B006 |Bedroom |
|B007 |Bedroom |
|B008 |Bedroom |
+---------+---------------+
Output
+-------------+-----------+-----+
|item_category|day_of_week|count|
+-------------+-----------+-----+
|Outdoors |Wednesday |2 |
|Bedroom |Monday |1 |
|Outdoors |Monday |2 |
|Bedroom |Tuesday |2 |
|Kitchen |Sunday |1 |
|Bedroom |Sunday |1 |
+-------------+-----------+-----+
Solution
select
i.item_category,
to_char(order_datetime::date, 'Day') as day_of_week,
count(b.bid_id)
from bids b
left join items i on b.item_id = i.item_id
group by i.item_category, to_char(order_datetime::date, 'Day')
order by 2 ASC;
Amazon/AWS SQL Interview Question 52: Find the earliest bid_id for each customer
Prompt: Write a query to get the earliest bid id for each customer for each date that placed a bid.
Table: bids +------+-----------+--------------------+-------+--------------+ |bid_id|customer_id| order_datetime |item_id|order_quantity| +------+-----------+--------------------+-------+--------------+ |A-001 |32483 |2021-12-15 09:15:22 |B000 |3 | |A-002 |21456 |2022-01-10 09:28:35 |B001 |1 | |A-003 |21456 |2022-01-09 09:28:35 |B005 |1 | |A-004 |42491 |2022-01-16 02:52:07 |B008 |2 | |A-005 |42491 |2022-01-18 02:52:07 |B008 |2 | |A-006 |42491 |2022-01-18 02:52:07 |B008 |5 | |A-007 |21456 |2022-01-17 09:28:35 |B000 |1 | |A-008 |21456 |2022-01-17 10:28:35 |B008 |3 | |A-009 |21456 |2022-01-19 10:28:35 |B000 |2 | +------+-----------+--------------------+-------+--------------+
Table: items
+---------+---------------+
| item_id | item_category |
+---------+---------------+
|B000 |Outdoors |
|B001 |Outdoors |
|B002 |Outdoors |
|B003 |Kitchen |
|B004 |Kitchen |
|B005 |Kitchen |
|B006 |Bedroom |
|B007 |Bedroom |
|B008 |Bedroom |
+---------+---------------+
Output
+-----------+------------------------+---------+
|customer_id|order_datetime |first_bid|
+-----------+------------------------+---------+
|21456 |2022-01-09T00:00:00.000Z|A-003 |
|21456 |2022-01-10T00:00:00.000Z|A-002 |
|21456 |2022-01-17T00:00:00.000Z|A-007 |
|21456 |2022-01-19T00:00:00.000Z|A-009 |
|32483 |2021-12-15T00:00:00.000Z|A-001 |
|42491 |2022-01-16T00:00:00.000Z|A-004 |
|42491 |2022-01-18T00:00:00.000Z|A-006 |
|42491 |2022-01-18T00:00:00.000Z|A-005 |
+-----------+------------------------+---------+
Solution
with rank_bids as (
select
customer_id,
order_datetime,
bid_id,
rank() over(partition by customer_id, order_datetime::date order by order_datetime) as rank_
from bids
)
select
customer_id,
order_datetime::date,
bid_id as first_bid
from rank_bids
where rank_ = 1;
Amazon/AWS SQL Interview Question 47: Inventory missing
Prompt: Write a query to find how many units of inventory in the company are either missing or have incomplete dimensions?
Table: factory_inventory +---------+---------+-------+ | factory | product | units | +---------+---------+-------+ |ABC1 |XYZ |3 | |ABC2 |PQR |1 | |ABC3 |GHI |2 | |ABC3 |JKL |5 | |ABC2 |MNO |2 | |ABC2 |STU |3 | |ABC2 |AAA |4 | |ABC2 |VWX |1 | |ABC1 |VWX |1 | |ABC1 |ZYX |17 | |ABC1 |YYY |13 | +---------+---------+-------+
Table: product_dimension_inches
+-----+-----+-----+---------+
| W | L | H | product |
+-----+-----+-----+---------+
|12 |10 |8 |XYZ |
|4 |3 |3 |PQR |
|14 |11 |2 |GHI |
|8 |10 |12 |JKL |
|8 |10 |10 |MNO |
|8 |10 |10 |STU |
|8 |10 |10 |VWX |
|8 |10 |NULL |YYY |
+-----+-----+-----+---------+
Output
+------------------+
|missing_dimensions|
+------------------+
|34 |
+------------------+
Solution
select
sum(f.units) as missing_dimensions
from factory_inventory f
left join product_dimension_inches d on f.product = d.product
where d.W is null or d.L is null or d.H is null;
Amazon/AWS SQL Interview Question 45: Cubic feet of volume
Prompt: Write a query to find how much cubic feet of volume does the inventory occupy in each factory
Table: factory_inventory +---------+---------+-------+ | factory | product | units | +---------+---------+-------+ |ABC1 |XYZ |3 | |ABC2 |PQR |1 | |ABC3 |GHI |2 | |ABC3 |JKL |5 | |ABC2 |MNO |2 | |ABC2 |STU |3 | |ABC2 |AAA |4 | |ABC2 |VWX |1 | |ABC1 |VWX |1 | |ABC1 |ZYX |17 | |ABC1 |YYY |13 | +---------+---------+-------+
Table: product_dimension_inches
+-----+-----+-----+---------+
| W | L | H | product |
+-----+-----+-----+---------+
|12 |10 |8 |XYZ |
|4 |3 |3 |PQR |
|14 |11 |2 |GHI |
|8 |10 |12 |JKL |
|8 |10 |10 |MNO |
|8 |10 |10 |STU |
|8 |10 |10 |VWX |
|8 |10 |NULL |YYY |
+-----+-----+-----+---------+
Output
+-------+----------------+
|factory|total_cubic_feet|
+-------+----------------+
|ABC2 |4836 |
|ABC3 |5416 |
|ABC1 |3680 |
+-------+----------------+
Solution
select
f.factory,
sum(f.units * d.W * d.L * d.H) as total_cubic_feet
from factory_inventory f
left join product_dimension_inches d on f.product = d.product
group by f.factory;
Amazon/AWS SQL Interview Question 37: Buildings with no employees
Prompt: Write a query to return a list of buildings where there are no employees in them.
Table: employee +------+----------+---------+----------+------+----+----------+-----------+ |emp_id|first_name|last_name|manager_id|salary|dept|start_date|building_id| +------+----------+---------+----------+------+----+----------+-----------+ |1 |hector |bush |1 |40000 |ops |2018-03-05|1 | |2 |kim |stewart |1 |31000 |eng |2019-04-05|2 | |3 |peter |thompson |1 |31700 |ds |2019-02-05|3 | |4 |thomas |henderson|1 |32000 |ops |2018-03-05|2 | |5 |john |free |1 |35000 |eng |2019-03-08|3 | |6 |tara |glad |5 |32000 |ds |2019-01-12|1 | |7 |linda |bruss |5 |30000 |ops |2020-04-19|3 | |8 |forest |gump |6 |29000 |eng |2021-04-19|1 | |9 |luke |smith |7 |28000 |ds |2019-06-24|2 | +------+----------+---------+----------+------+----+----------+-----------+
Table: building +-----------+--------------+ |building_id|building_name | +-----------+--------------+ |1 |ss | |2 |vs | |3 |ts | |4 |zs | +-----------+--------------+
Output
+-----------+-------------+
|building_id|building_name|
+-----------+-------------+
|4 |ZS |
+-----------+-------------+
Solution
select
b.building_id,
b.building_name
from building b
left join employee e on b.building_id = e.building_id
where e.emp_id is null;
Amazon/AWS SQL Interview Question 35: Find the total employees
Prompt: Write a query to find the total count of employees.
Table: employee +------+----------+---------+----------+------+----+----------+-----------+ |emp_id|first_name|last_name|manager_id|salary|dept|start_date|building_id| +------+----------+---------+----------+------+----+----------+-----------+ |1 |hector |bush |1 |40000 |ops |2018-03-05|1 | |2 |kim |stewart |1 |31000 |eng |2019-04-05|2 | |3 |peter |thompson |1 |31700 |ds |2019-02-05|3 | |4 |thomas |henderson|1 |32000 |ops |2018-03-05|2 | |5 |john |free |1 |35000 |eng |2019-03-08|3 | |6 |tara |glad |5 |32000 |ds |2019-01-12|1 | |7 |linda |bruss |5 |30000 |ops |2020-04-19|3 | |8 |forest |gump |6 |29000 |eng |2021-04-19|1 | |9 |luke |smith |7 |28000 |ds |2019-06-24|2 | +------+----------+---------+----------+------+----+----------+-----------+
Table: building +-----------+--------------+ |building_id|building_name | +-----------+--------------+ |1 |ss | |2 |vs | |3 |ts | |4 |zs | +-----------+--------------+
Output
+-------+
|count |
+-------+
|9 |
+-------+
Solution
select count(distinct emp_id)
from employee;
Amazon/AWS SQL Interview Question 42: Number of times solved
Prompt: Write a query to find the number of times each question was solved and the total minutes needed and the difficulty level in the month of December 2018
Table: questions +-------------+-------+--------+--------------------+ | question_id | level | name | added_date | +-------------+-------+--------+--------------------+ |453 |easy |superbad|2021-04-05 10:00:00 | |558 |medium |matrix |2021-05-09 12:00:00 | |841 |hard |lucky |2019-06-03 16:00:00 | +-------------+-------+--------+--------------------+
Table: members +---------+-------------+---------------------+---------+ | user_id | question_id | start_time | minutes | +---------+-------------+---------------------+---------+ |1 |453 |2021-04-06 11:00:00 |45 | |2 |558 |2021-05-10 10:00:00 |80 | |3 |841 |2019-06-04 03:00:00 |35 | |4 |453 |2021-04-06 15:00:00 |53 | |5 |841 |2019-06-04 12:00:00 |90 | |6 |841 |2018-12-04 12:00:00 |3 | |5 |841 |2018-12-07 12:00:00 |90 | |3 |558 |2018-12-11 12:00:00 |45 | +---------+-------------+---------------------+---------+
Output
+-----------+------+-----+-------------+
|question_id|level |count|total_minutes|
+-----------+------+-----+-------------+
|841 |hard |2 |93 |
|558 |medium|1 |45 |
+-----------+------+-----+-------------+
Solution
select
q.question_id,
q."level",
count(user_id),
sum(minutes) as total_minutes
from questions q
left join members m on q.question_id = m.question_id
where date_trunc('month', m.start_time)::date = '2018-12-01'::date
group by
q.question_id,
q."level";
Want to practice more questions like this with on-demand support and a dynamic SQL playground? Learn more here!
We’ve analyzed over 50,000 interviews from the latest data science, data analyst, data engineer, business analyst, and business intelligence engineer roles being filled by pre-IPO to Fortune 500 companies at Big Tech Interviews (BTI) to curate an exclusive list of the latest SQL interview questions and solutions so you can ace your next interview!