Power BI - Essential SQL Queries for Sales

Essential SQL Queries for Sales

 Customer & Orders

• List all orders for a customer:

SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE CustomerID = 101;

• Top 10 customers by sales:

SELECT CustomerID, SUM(TotalAmount) AS TotalSales
FROM Orders
GROUP BY CustomerID
ORDER BY TotalSales DESC
LIMIT 10;

• Order count by region:

SELECT Region, COUNT(OrderID) AS OrderCount
FROM Orders
GROUP BY Region;

---

Revenue Analysis

• Monthly revenue trend:

SELECT YEAR(OrderDate) AS Year, MONTH(OrderDate) AS Month,
       SUM(TotalAmount) AS MonthlyRevenue
FROM Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY Year, Month;

• Revenue by product category:

SELECT Category, SUM(TotalAmount) AS Revenue
FROM Orders o
JOIN Products p ON o.ProductID = p.ProductID
GROUP BY Category;

• Year-over-year growth:

SELECT YEAR(OrderDate) AS Year,
       SUM(TotalAmount) AS Revenue,
       LAG(SUM(TotalAmount)) OVER (ORDER BY YEAR(OrderDate)) AS PrevYearRevenue,
       (SUM(TotalAmount) - LAG(SUM(TotalAmount)) OVER (ORDER BY YEAR(OrderDate))) * 100.0 /
       LAG(SUM(TotalAmount)) OVER (ORDER BY YEAR(OrderDate)) AS GrowthRate
FROM Orders
GROUP BY YEAR(OrderDate);

---

Sales Performance

• Sales by salesperson:

SELECT SalesPersonID, SUM(TotalAmount) AS SalesTotal
FROM Orders
GROUP BY SalesPersonID;

• Average order value (AOV):

SELECT AVG(TotalAmount) AS AvgOrderValue
FROM Orders;

• Conversion rate (quotes → orders):

SELECT COUNT(DISTINCT OrderID) * 100.0 / COUNT(DISTINCT QuoteID) AS ConversionRate
FROM Quotes q
LEFT JOIN Orders o ON q.QuoteID = o.QuoteID;

---

Operational Sales Insights

• Pending deliveries:

SELECT OrderID, DeliveryStatus
FROM Orders
WHERE DeliveryStatus = 'Pending';

• Return rate by product:

SELECT ProductID,
       SUM(CASE WHEN ReturnFlag = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS ReturnRate
FROM OrderDetails
GROUP BY ProductID;

---

Key Notes

• Indexes: Use on CustomerID, OrderDate, and ProductID for performance.
• Dashboards: These queries feed into KPIs like revenue growth, AOV, conversion rate.
• Compliance: Always anonymize customer data when sharing outside the organization.

---