Amazon-style Company ChallengeBeginnerVerified answerSQLite live

Recently Hired Warehouse Employees

Which warehouse employees were hired in 2024, and which warehouse are they assigned to?

  • Joins
  • Date analysis
  • Filtering
  • Sorting

Challenge brief

Understand the request

HR Operations is onboarding new warehouse staff and needs to confirm which associates joined in 2024.

List warehouse employees hired in 2024 with their warehouse details.

Return

  • first_name
  • last_name
  • hire_date
  • warehouse_name
  • warehouse_location

Constraints

  • Only include employees hired in calendar year 2024
  • Only warehouse employees (those with a warehouse_id)
  • Order by hire_date descending (most recent first)

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

employees

  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)
  • hire_dateDATE
  • warehouse_idINTEGER

warehouses

  • warehouse_idINTEGER
  • warehouse_nameVARCHAR(100)
  • locationVARCHAR(100)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

You need names and hire dates from employees, and warehouse details from warehouses. The hire_date column stores dates as text in YYYY-MM-DD format. You can extract the year using strftime('%Y', hire_date).

Hint 2

INNER JOIN employees to warehouses on warehouse_id — this automatically excludes non-warehouse staff. Add WHERE strftime('%Y', hire_date) = '2024' to keep only 2024 hires. Order by hire_date DESC.

Hint 3

Scaffold: join employees to warehouses, filter the extracted hire year to 2024, and return newest hires first.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT e.first_name, e.last_name, e.hire_date, w.warehouse_name, w.location AS warehouse_location FROM employees e INNER JOIN warehouses w ON e.warehouse_id = w.warehouse_id WHERE strftime('%Y', e.hire_date) = '2024' ORDER BY e.hire_date DESC;

Why this works

strftime('%Y', hire_date) extracts the 4-digit year from a date string. INNER JOIN on warehouse_id excludes the 5 non-warehouse employees. Two employees — Tom Wilson and Rachel Green — were hired in 2024.

Success check

2 employees — both hired in 2024 at SEA1 and LAX1

Expected result

Use this output to verify values, aliases, ordering, and row count.

first_namelast_namehire_datewarehouse_namewarehouse_location
TomWilson2024-06-20SEA1Seattle, WA
RachelGreen2024-03-15LAX1Los Angeles, CA

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.