Northwind Intelligence

Analysis Questions

12 targeted questions with SQL logic that drives the dashboard.

SQL joinsAggregationDecision support

Q1

Which product categories generate the highest revenue?

SELECT c.categoryName, SUM(od.unitPrice * od.quantity * (1 - od.discount)) AS revenue
FROM OrderDetail od
JOIN Product p ON od.productId = p.productId
JOIN Category c ON p.categoryId = c.categoryId
GROUP BY c.categoryName
ORDER BY revenue DESC;

Q2

Which individual products are the top revenue drivers?

SELECT p.productName, SUM(od.unitPrice * od.quantity * (1 - od.discount)) AS revenue
FROM OrderDetail od
JOIN Product p ON od.productId = p.productId
GROUP BY p.productId
ORDER BY revenue DESC
LIMIT 10;

Q3

Which customers contribute the most revenue?

SELECT cu.companyName, SUM(od.unitPrice * od.quantity * (1 - od.discount)) AS revenue
FROM SalesOrder so
JOIN OrderDetail od ON so.orderId = od.orderId
JOIN Customer cu ON so.custId = cu.custId
GROUP BY cu.custId
ORDER BY revenue DESC
LIMIT 10;

Q4

How does sales revenue trend over time by month?

SELECT DATE_FORMAT(so.orderDate, '%Y-%m') AS month,
SUM(od.unitPrice * od.quantity * (1 - od.discount)) AS revenue
FROM SalesOrder so
JOIN OrderDetail od ON so.orderId = od.orderId
GROUP BY month
ORDER BY month;

Q5

Which shipping countries generate the most revenue?

SELECT so.shipCountry, SUM(od.unitPrice * od.quantity * (1 - od.discount)) AS revenue
FROM SalesOrder so
JOIN OrderDetail od ON so.orderId = od.orderId
GROUP BY so.shipCountry
ORDER BY revenue DESC;

Q6

Which employees generate the highest sales impact?

SELECT CONCAT(e.firstname, ' ', e.lastname) AS employee,
SUM(od.unitPrice * od.quantity * (1 - od.discount)) AS revenue
FROM SalesOrder so
JOIN OrderDetail od ON so.orderId = od.orderId
JOIN Employee e ON so.employeeId = e.employeeId
GROUP BY e.employeeId
ORDER BY revenue DESC;

Q7

What is the average shipping lead time by shipper?

SELECT s.companyName, AVG(DATEDIFF(so.shippedDate, so.orderDate)) AS avgShipDays
FROM SalesOrder so
JOIN Shipper s ON so.shipperid = s.shipperId
WHERE so.shippedDate IS NOT NULL
GROUP BY s.shipperId
ORDER BY avgShipDays;

Q8

Which suppliers contribute the most revenue?

SELECT s.companyName, SUM(od.unitPrice * od.quantity * (1 - od.discount)) AS revenue
FROM OrderDetail od
JOIN Product p ON od.productId = p.productId
JOIN Supplier s ON p.supplierId = s.supplierId
GROUP BY s.supplierId
ORDER BY revenue DESC;

Q9

Which products are below reorder levels and need attention?

SELECT p.productName, p.unitsInStock, p.reorderLevel
FROM Product p
WHERE p.unitsInStock <= p.reorderLevel
ORDER BY p.unitsInStock ASC;

Q10

What is the average discount applied by category?

SELECT c.categoryName, AVG(od.discount) AS avgDiscount
FROM OrderDetail od
JOIN Product p ON od.productId = p.productId
JOIN Category c ON p.categoryId = c.categoryId
GROUP BY c.categoryName
ORDER BY avgDiscount DESC;

Q11

How many customers are served in each country?

SELECT country, COUNT(*) AS customers
FROM Customer
GROUP BY country
ORDER BY customers DESC;

Q12

What is the average order value by customer?

SELECT cu.companyName,
AVG(orderTotals.orderValue) AS avgOrderValue
FROM (
  SELECT so.orderId, so.custId,
  SUM(od.unitPrice * od.quantity * (1 - od.discount)) AS orderValue
  FROM SalesOrder so
  JOIN OrderDetail od ON so.orderId = od.orderId
  GROUP BY so.orderId, so.custId
) AS orderTotals
JOIN Customer cu ON orderTotals.custId = cu.custId
GROUP BY cu.custId
ORDER BY avgOrderValue DESC;