-- Marcus Webb — Pedal Works Analysis

-- Question 1
SELECT customers.firstName, customers.lastName, customers.email FROM bikeshop.customers WHERE customers.city = 'Green Bay';

-- Question 2
SELECT bikes.brand, bikes.model, bikes.bikeType, bikes.purchasePrice FROM bikeshop.bikes ORDER BY bikes.purchasePrice DESC;

-- Question 3
SELECT partName, partNumber, category, unitCost FROM parts WHERE partName LIKE '%bundle%' ORDER BY unitCost ASC;

-- Question 4
SELECT employees.firstName, employees.lastName, employees.role, employees.hourlyRate FROM bikeshop.employees WHERE employees.role = 'Technician' ORDER BY employees.hourlyRate DESC;

-- Question 5
SELECT repairOrders.repairOrderId, repairOrders.dropOffDate, repairOrders.status, employees.firstName, employees.lastName FROM bikeshop.repairOrders JOIN bikeshop.employees ON repairOrders.employeeId = employees.employeeId WHERE repairOrders.status = 'In Progress' ORDER BY repairOrders.dropOffDate ASC;

-- Question 6
SELECT stores.storeName, stores.city, COUNT(repairOrders.repairOrderId) AS totalRepairOrders FROM bikeshop.stores LEFT JOIN bikeshop.repairOrders ON stores.storeId = repairOrders.storeId GROUP BY stores.storeId, stores.storeName, stores.city ORDER BY totalRepairOrders DESC;

-- Question 7
-- (not attempted)

-- Question 8
SELECT customers.firstName, customers.lastName, customers.city, COUNT(DISTINCT repairOrders.repairOrderId) AS totalRepairOrders, SUM(repairItems.lineTotal) AS totalRevenue FROM bikeshop.customers JOIN bikeshop.bikes ON customers.customerId = bikes.customerId JOIN bikeshop.repairOrders ON bikes.bikeId = repairOrders.bikeId JOIN bikeshop.repairItems ON repairOrders.repairOrderId = repairItems.repairOrderId GROUP BY customers.customerId, customers.firstName, customers.lastName, customers.city HAVING SUM(repairItems.lineTotal) > 500 ORDER BY totalRevenue DESC;
