latest-amazon-sql-interview

10 Latest Amazon / AWS SQL Interview Questions

Tim S

Tim S

Tim currently works at FAANG as a data analyst. He's been in the industry for 5+ years and writes for Big Tech Interviews on the weekends. His favorite language is SQL and calls Denver, CO home.

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! 

Similar Articles