Analyzing Employee Order Performance and Timeliness in Northwind Database

Medium
2
55.6% Acceptance

This exercise utilizes the following tables:

  • orders
  • employees

Task

Your task is to write a SQL query that accomplishes the following:

  1. Retrieve the first and last names of employees.
  2. Count the number of orders handled by each employee and display it in a column named num_orders.
  3. Add another column named Shipped that indicates whether an order has been shipped "On Time" or "Late".
  4. The output should be sorted first by the employee's last name, then by their first name, and finally in descending order by the number of orders.

Key Concepts

  • JOIN: You'll need to use a JOIN operation to correlate data between the orders and employees tables.
  • Aggregate Functions: Utilize SQL aggregate functions like COUNT() to sum up the number of orders.
  • Conditional Logic: Incorporate conditional logic within your query to differentiate between "On Time" and "Late" shipments.
  • Sorting: Make use of the ORDER BY clause for sorting the records.

The column names must be first_name, last_name, num_orders and shipped

Good luck!