-- SQL Server query to retrieve staff sales data.

-- Selects staff's first name, last name, email, and the total quantity of items sold by each staff member.

-- Selects staffs.first_name and aliases it as 'First Name'.
-- Selects staffs.last_name and aliases it as 'Surname'.
-- Selects staffs.email and aliases it as 'Email'.
-- Calculates the sum of order_items.quantity and aliases it as '[Total quantity sold]'.

-- Joins the BikeStores.orders table with BikeStores.order_items table using orders.order_id and order_items.order_id.
-- This join ensures that order details are linked to the corresponding order items.

-- Joins the result with BikeStores.staffs table using orders.staff_id and staffs.staff_id.
-- This join links the orders to the staff members who processed them.

-- Groups the results by staff's first name, last name, and email.
-- This allows for the aggregation of total quantities sold per staff member.

-- Orders the results by the total quantity sold in ascending order.
-- This presents the staff with the lowest sales first.

SELECT 
    staffs.first_name AS 'First Name', 
    staffs.last_name AS 'Surname', 
    staffs.email AS 'Email', 
    SUM(order_items.quantity) AS [Total quantity sold]
FROM 
    BikeStores..orders
INNER JOIN
    BikeStores..order_items ON orders.order_id = order_items.order_id
INNER JOIN 
    BikeStores..staffs ON orders.staff_id = staffs.staff_id
GROUP BY 
    staffs.first_name, 
    staffs.last_name, 
    staffs.email
ORDER BY 
    [Total quantity sold];

-- The query retrieves sales data for each staff member, showing their name, email, and the total quantity of items they have sold.  The data is sourced from three tables: orders, order_items, and staffs, which are joined to link order information with staff details. The results are grouped by staff member and ordered by total quantity sold.