Power BI - Essential SQL Queries for Finance

 Essential SQL Queries for Finance 

1. Transactions & Accounts

• List all transactions for a customer:

SELECT TransactionID, CustomerID, TransactionDate, Amount, TransactionType
FROM Transactions
WHERE CustomerID = 101;

• Total balance per customer:

SELECT CustomerID, SUM(Amount) AS Balance
FROM Transactions
GROUP BY CustomerID;

• Monthly transaction summary:

SELECT YEAR(TransactionDate) AS Year, MONTH(TransactionDate) AS Month,
       SUM(Amount) AS TotalAmount
FROM Transactions
GROUP BY YEAR(TransactionDate), MONTH(TransactionDate);

---

2. Revenue & Expenses

• Total revenue by product:

SELECT ProductID, SUM(Amount) AS Revenue
FROM Sales
GROUP BY ProductID;

• Expense breakdown by category:

SELECT ExpenseCategory, SUM(Amount) AS TotalExpense
FROM Expenses
GROUP BY ExpenseCategory;

• Profit calculation (Revenue – Expenses):

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

---

3. Budget & Forecasting

• Compare actual vs budget:

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

• Year-over-year growth:

SELECT YEAR(TransactionDate) AS Year,
       SUM(Amount) AS TotalRevenue,
       LAG(SUM(Amount)) OVER (ORDER BY YEAR(TransactionDate)) AS PrevYearRevenue,
       (SUM(Amount) - LAG(SUM(Amount)) OVER (ORDER BY YEAR(TransactionDate))) * 100.0 /
       LAG(SUM(Amount)) OVER (ORDER BY YEAR(TransactionDate)) AS GrowthRate
FROM Sales
GROUP BY YEAR(TransactionDate);

---

4. Risk & Compliance

• Flag transactions above threshold (e.g., fraud detection):

SELECT TransactionID, CustomerID, Amount
FROM Transactions
WHERE Amount > 100000;

• Identify inactive accounts:

SELECT CustomerID
FROM Accounts
WHERE LastTransactionDate < DATEADD(YEAR, -1, GETDATE());

---

5. Advanced Joins

• Customer details with transaction history:

SELECT c.CustomerID, c.CustomerName, t.TransactionID, t.Amount, t.TransactionDate
FROM Customers c
JOIN Transactions t ON c.CustomerID = t.CustomerID;

• Revenue by region:

SELECT r.RegionName, SUM(s.Amount) AS Revenue
FROM Sales s
JOIN Regions r ON s.RegionID = r.RegionID
GROUP BY r.RegionName;

---

Key Use Cases

• Financial dashboards (Power BI): Revenue, expenses, profit trends.
• Compliance reports: High-value transactions, inactive accounts.
• Forecasting models: Year-over-year growth, budget variance.

---