Power BI - Essential SQL Queries for Executive Leadership

 Essential SQL Queries for Executive Leadership 

Company-Wide Performance

• Revenue vs. Expenses (Profitability):

SELECT (SELECT SUM(Amount) FROM Sales) AS TotalRevenue,
       (SELECT SUM(Amount) FROM Expenses) AS TotalExpenses,
       (SELECT SUM(Amount) FROM Sales) - (SELECT SUM(Amount) FROM Expenses) AS Profit;

• 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);

---

Workforce Insights

• Headcount by department:

SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;

• Attrition rate company-wide:

SELECT SUM(CASE WHEN Attrition = 'Yes' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS AttritionRate
FROM Employees;

• Average tenure of employees:

SELECT AVG(YearsAtCompany) AS AvgTenure
FROM Employees;

---

Financial Oversight

• Budget vs. actual spend by department:

SELECT Department, SUM(BudgetAmount) AS Budget,
       SUM(ActualAmount) AS Actual,
       SUM(ActualAmount) - SUM(BudgetAmount) AS Variance
FROM Finance
GROUP BY Department;

• Top 5 cost centers:

SELECT Department, SUM(Cost) AS TotalCost
FROM OperationsCosts
GROUP BY Department
ORDER BY TotalCost DESC
LIMIT 5;

---

Sales & Customer Metrics

• Top 10 customers by revenue:

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

• Customer satisfaction score (company-wide):

SELECT AVG(SatisfactionScore) AS AvgSatisfaction
FROM Feedback;

• 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 Efficiency

• On-time delivery rate:

SELECT SUM(CASE WHEN DeliveryStatus = 'On-Time' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS OnTimeRate
FROM Deliveries;

• Defect rate in production:

SELECT ProductID,
       SUM(CASE WHEN DefectFlag = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS DefectRate
FROM ProductionQuality
GROUP BY ProductID;

---

Key Notes for Leadership Dashboards

• Indexes: Optimize queries with indexes on DepartmentID, CustomerID, OrderDate, ProductID.
• Dashboards: These queries feed into executive dashboards (Power BI, Tableau) showing KPIs like revenue growth, attrition, satisfaction, and operational efficiency.
• Best practice: Always validate cross-department joins to ensure consistency in reporting.

---