DML.sql

--=============================================Insert Section===============================
-- Insert records into Owners table
USE ResidentialBusiness;
GO
INSERT INTO rb.Owners (OwnerID, OwnerName, ContactNumber, Email)
VALUES
(1, 'Abul Kalam', '01711111111', 'abul.kalam@gmail.com'),
(2, 'Salma Akter', '01811111112', 'salma.akter@gmail.com'),
(3, 'Faruk Ahmed', '01911111113', 'faruk.ahmed@gmail.com'),
(4, 'Khaleda Begum', '01711111114', 'khaleda.begum@gmail.com'),
(5, 'Nurul Haque', '01811111115', 'nurul.haque@gmail.com'),
(6, 'Hasina Parveen', '01911111116', 'hasina.parveen@gmail.com'),
(7, 'Anisur Rahman', '01711111117', 'anisur.rahman@gmail.com'),
(8, 'Tahmina Sultana', '01811111118', 'tahmina.sultana@gmail.com'),
(9, 'Rezaul Karim', '01911111119', 'rezaul.karim@gmail.com'),
(10, 'Shahnaz Rahman', '01711111120', 'shahnaz.rahman@gmail.com');

-- Insert records into Properties table
USE ResidentialBusiness;
GO
INSERT INTO rb.Properties (PropertyID, PropertyName, [Location], Price, [Description], SquareFootage)
VALUES
(1, 'Green Valley', 'Dhaka', 20000000, 'Modern Apartment', 1500),
(2, 'Sunshine Villa', 'Chittagong', 18000000, 'Luxury House', 1800),
(3, 'Lakeview Tower', 'Sylhet', 25000000, 'Scenic Apartment', 2000),
(4, 'Rose Garden', 'Khulna', 12000000, 'Affordable Flat', 1200),
(5, 'Ocean Breeze', 'Cox''s Bazar', 30000000, 'Sea View', 2200),
(6, 'Hill Top', 'Bandarban', 16000000, 'Mountain Retreat', 1400),
(7, 'City Heights', 'Dhaka', 27000000, 'Prime Location', 1700),
(8, 'Golden Horizon', 'Rajshahi', 15000000, 'Quiet Neighborhood', 1300),
(9, 'Riverside', 'Barisal', 20000000, 'Riverside Apartment', 1800),
(10, 'Forest View', 'Sylhet', 22000000, 'Nature Inspired', 1900);

-- Insert records into LeasesFromOwner table
USE ResidentialBusiness;
GO
INSERT INTO rb.LeasesFromOwner (LeaseFromOwnerID, OwnerID, PropertyID, StartDate, EndDate, Rent)
VALUES
(1, 1, 1, '2023-01-01', '2023-12-31', 50000),
(2, 2, 2, '2023-02-01', '2023-12-31', 45000),
(3, 3, 3, '2023-03-01', '2023-12-31', 55000),
(4, 4, 4, '2023-04-01', '2023-12-31', 35000),
(5, 5, 5, '2023-05-01', '2023-12-31', 70000),
(6, 6, 6, '2023-06-01', '2023-12-31', 40000),
(7, 7, 7, '2023-07-01', '2023-12-31', 65000),
(8, 8, 8, '2023-08-01', '2023-12-31', 30000),
(9, 9, 9, '2023-09-01', '2023-12-31', 60000),
(10, 10, 10, '2023-10-01', '2023-12-31', 55000);

-- Insert records into Apartments table
USE ResidentialBusiness;
GO
INSERT INTO rb.Apartments (ApartmentID, PropertyID, FloorNumber, UnitNumber)
VALUES
(1, 1, 2, 'A-201'),
(2, 2, 3, 'B-301'),
(3, 3, 1, 'C-101'),
(4, 4, 2, 'D-202'),
(5, 5, 4, 'E-401'),
(6, 6, 1, 'F-102'),
(7, 7, 3, 'G-303'),
(8, 8, 2, 'H-204'),
(9, 9, 1, 'I-105'),
(10, 10, 3, 'J-306');

-- Insert records into Residents table
USE ResidentialBusiness;
GO
INSERT INTO rb.Residents (ResidentID, ResidentName, ContactNumber, Email)
VALUES
(1, 'Rafiqul Islam', '01611111121', 'rafiqul.islam@gmail.com'),
(2, 'Momena Khatun', '01711111122', 'momena.khatun@gmail.com'),
(3, 'Jahangir Alam', '01811111123', 'jahangir.alam@gmail.com'),
(4, 'Shamim Ara', '01911111124', 'shamim.ara@gmail.com'),
(5, 'Karim Ullah', '01611111125', 'karim.ullah@gmail.com'),
(6, 'Nazma Banu', '01711111126', 'nazma.banu@gmail.com'),
(7, 'Mokhlesur Rahman', '01811111127', 'mokhlesur.rahman@gmail.com'),
(8, 'Taher Ali', '01911111128', 'taher.ali@gmail.com'),
(9, 'Sabina Yasmin', '01611111129', 'sabina.yasmin@gmail.com'),
(10, 'Selim Ahmed', '01711111130', 'selim.ahmed@gmail.com');

-- Insert records into LeasesToResidents table
USE ResidentialBusiness;
GO
INSERT INTO rb.LeasesToResidents (LeaseToResidentID, ApartmentID, ResidentID, StartDate, EndDate, Rent)
VALUES
(1, 1, 1, '2023-01-01', '2023-12-31', 20000),
(2, 2, 2, '2023-02-01', '2023-12-31', 18000),
(3, 3, 3, '2023-03-01', '2023-12-31', 22000),
(4, 4, 4, '2023-04-01', '2023-12-31', 15000),
(5, 5, 5, '2023-05-01', '2023-12-31', 30000),
(6, 6, 6, '2023-06-01', '2023-12-31', 16000),
(7, 7, 7, '2023-07-01', '2023-12-31', 25000),
(8, 8, 8, '2023-08-01', '2023-12-31', 14000),
(9, 9, 9, '2023-09-01', '2023-12-31', 20000),
(10, 10, 10, '2023-10-01', '2023-12-31', 22000)

-- Insert records into MaintenanceRequests table
USE ResidentialBusiness;
GO
INSERT INTO rb.MaintenanceRequests (RequestID, ApartmentID, ResidentID, [Status], Descriptions)
VALUES
(1, 1, 1, 'Pending', 'Leakage'),
(2, 2, 2, 'Completed', 'Electrical Issue'),
(3, 3, 3, 'In Progress', 'Broken Door'),
(4, 4, 4, 'Pending', 'Plumbing Issue'),
(5, 5, 5, 'Completed', 'Painting'),
(6, 6, 6, 'In Progress', 'AC Repair'),
(7, 7, 7, 'Pending', 'Gas Line Issue'),
(8, 8, 8, 'Completed', 'Window Replacement'),
(9, 9, 9, 'In Progress', 'Water Supply'),
(10, 10, 10, 'Pending', 'Flooring');

-- Insert records into Employees table
USE ResidentialBusiness;
GO
INSERT INTO rb.Employees (EmployeeID, [Name], Position, ContactNumber, Email, HireDate, Salary)
VALUES
(1, 'Mehedi Hasan', 'Manager', '01722222221', 'mehedi.hasan@gmail.com', '2021-03-15', 50000),
(2, 'Sumi Akter', 'Accountant', '01822222222', 'sumi.akter@gmail.com', '2022-06-10', 40000),
(3, 'Nazmul Huda', 'Technician', '01922222223', 'nazmul.huda@gmail.com', '2021-11-01', 35000),
(4, 'Tania Rahman', 'Receptionist', '01722222224', 'tania.rahman@gmail.com', '2022-01-25', 30000),
(5, 'Fahim Hossain', 'Supervisor', '01822222225', 'fahim.hossain@gmail.com', '2021-07-20', 45000),
(6, 'Anika Sultana', 'Technician', '01922222226', 'anika.sultana@gmail.com', '2021-09-30', 35000),
(7, 'Rashed Mahmud', 'Security', '01722222227', 'rashed.mahmud@gmail.com', '2022-03-15', 25000),
(8, 'Shahidul Islam', 'Cleaner', '01822222228', 'shahidul.islam@gmail.com', '2021-04-10', 20000),
(9, 'Mili Akter', 'Office Assistant', '01922222229', 'mili.akter@gmail.com', '2022-08-05', 25000),
(10, 'Abdullah Al Mamun', 'Technician', '01722222230', 'abdullah.mamun@gmail.com', '2021-05-20', 35000);

-- Insert records into Buy table
USE ResidentialBusiness;
GO
INSERT INTO rb.Buy (PaymentID, LeaseFromOwnerID, OwnerID, PropertyID, PaymentDate, PaymentMethod, Amount, EmployeeSalary)
VALUES
(1, 1, 1, 1, '2023-01-15', 'Cash', 2000000, 50000),
(2, 2, 2, 2, '2023-02-10', 'Bank Transfer', 1800000, 40000),
(3, 3, 3, 3, '2023-03-05', 'Cheque', 2500000, 35000),
(4, 4, 4, 4, '2023-04-20', 'Cash', 1200000, 30000),
(5, 5, 5, 5, '2023-05-25', 'Bank Transfer', 3000000, 45000),
(6, 6, 6, 6, '2023-06-30', 'Cheque', 1600000, 35000),
(7, 7, 7, 7, '2023-07-15', 'Cash', 2700000, 45000),
(8, 8, 8, 8, '2023-08-10', 'Bank Transfer', 1500000, 25000),
(9, 9, 9, 9, '2023-09-05', 'Cheque', 2000000, 20000),
(10, 10, 10, 10, '2023-10-20', 'Cash', 2200000, 35000);

-- Insert records into Sales table
USE ResidentialBusiness;
GO
INSERT INTO rb.Sales (ResidencePaymentID, LeaseToResidentID, ApartmentID, ResidentID, PaymentDate, PaymentMethod, Amount)
VALUES
(1, 1, 1, 1, '2023-01-10', 'Cash', 20000),
(2, 2, 2, 2, '2023-02-12', 'Bank Transfer', 18000),
(3, 3, 3, 3, '2023-03-08', 'Cheque', 22000),
(4, 4, 4, 4, '2023-04-15', 'Cash', 15000),
(5, 5, 5, 5, '2023-05-20', 'Bank Transfer', 30000),
(6, 6, 6, 6, '2023-06-18', 'Cheque', 16000),
(7, 7, 7, 7, '2023-07-22', 'Cash', 25000),
(8, 8, 8, 8, '2023-08-25', 'Bank Transfer', 14000),
(9, 9, 9, 9, '2023-09-28', 'Cheque', 20000),
(10, 10, 10, 10, '2023-10-30', 'Cash', 22000);

-- Insert records into ProfitMargin table
USE ResidentialBusiness;
GO
INSERT INTO rb.ProfitMargin (ProfitMarginID, Month, Year, GrossProfit, Expenses)
VALUES
(1, 1, 2023, 1000000, 500000),
(2, 2, 2023, 1200000, 600000),
(3, 3, 2023, 1500000, 700000),
(4, 4, 2023, 1400000, 650000),
(5, 5, 2023, 1600000, 800000),
(6, 6, 2023, 1700000, 850000),
(7, 7, 2023, 2000000, 1000000),
(8, 8, 2023, 1900000, 950000),
(9, 9, 2023, 1800000, 900000),
(10, 10, 2023, 2100000, 1050000);
go




----==================================================Select Section==================
-- Owners 
SELECT * FROM rb.Owners;
GO
-- Properties 
SELECT * FROM rb.Properties;
GO
-- LeasesFromOwner 
SELECT * FROM rb.LeasesFromOwner;
GO
-- Apartments 
SELECT * FROM rb.Apartments;
GO
-- Residents 
SELECT * FROM rb.Residents;
GO
-- LeasesToResidents 
SELECT * FROM rb.LeasesToResidents;
GO
-- MaintenanceRequests 
SELECT * FROM rb.MaintenanceRequests;
GO
-- Employees 
SELECT * FROM rb.Employees;
GO
-- Buy 
SELECT * FROM rb.Buy;
GO
-- Sales 
SELECT * FROM rb.Sales;
GO
-- ProfitMargin 
SELECT * FROM rb.ProfitMargin;
GO
-- Owners_Log
SELECT * FROM rb.Owners_Log
GO 
-- View
SELECT * FROM rb.vw_OwnerDetails;

----=================================================Innser Join==================
USE ResidentialBusiness;
GO
SELECT O.OwnerID, O.OwnerName, P.PropertyName
FROM rb.Owners O
INNER JOIN rb.LeasesFromOwner LFO ON O.OwnerID = LFO.OwnerID
INNER JOIN rb.Properties P ON LFO.PropertyID = P.PropertyID;
go 

----==================================================Left Join==================
USE ResidentialBusiness;
GO
SELECT O.OwnerID, O.OwnerName, P.PropertyName
FROM rb.Owners O
LEFT JOIN rb.LeasesFromOwner LFO ON O.OwnerID = LFO.OwnerID
LEFT JOIN rb.Properties P ON LFO.PropertyID = P.PropertyID;
go

----==================================================Right Join==================
USE ResidentialBusiness;
GO
SELECT P.PropertyID, P.PropertyName, O.OwnerName
FROM rb.Properties P
RIGHT JOIN rb.LeasesFromOwner LFO ON P.PropertyID = LFO.PropertyID
RIGHT JOIN rb.Owners O ON LFO.OwnerID = O.OwnerID;
go 

----===================================================Full Join==================
USE ResidentialBusiness;
GO
SELECT O.OwnerID, O.OwnerName, P.PropertyName
FROM rb.Owners O
FULL JOIN rb.LeasesFromOwner LFO ON O.OwnerID = LFO.OwnerID
FULL JOIN rb.Properties P ON LFO.PropertyID = P.PropertyID;


----==================================================Cross Join==================
USE ResidentialBusiness;
GO
SELECT O.OwnerID, O.OwnerName, P.PropertyID, P.PropertyName
FROM rb.Owners O
CROSS JOIN rb.Properties P;
go 

---==================================================Function Section======================
-----Aggregate Functions
--1. Total Salary for All Employees
USE ResidentialBusiness;
GO
SELECT SUM(Salary) AS TotalSalary
FROM rb.Employees;
go

--2. Average Rent Paid by Residents
USE ResidentialBusiness;
GO
SELECT SUM(Salary) AS TotalSalary
FROM rb.Employees;
go

--3. Maximum and Minimum Property Price
USE ResidentialBusiness;
GO
SELECT MAX(Price) AS MaximumPrice, MIN(Price) AS MinimumPrice
FROM rb.Properties;
go

---4. Total Profit and Average Profit for the Year 2023
USE ResidentialBusiness;
GO
SELECT 
    SUM(NetProfit) AS TotalProfit,
    AVG(NetProfit) AS AverageProfit
FROM rb.ProfitMargin
WHERE Year = 2023;
go

--5. Count of Apartments Per Property
USE ResidentialBusiness;
GO
SELECT PropertyID, COUNT(ApartmentID) AS TotalApartments
FROM rb.Apartments
GROUP BY PropertyID;
go 

---6. Total Revenue Collected from Sales
USE ResidentialBusiness;
GO
SELECT SUM(Amount) AS TotalRevenue
FROM rb.Sales;
go

---7. Count of Maintenance Requests by Status
USE ResidentialBusiness;
GO
SELECT [Status], COUNT(RequestID) AS TotalRequests
FROM rb.MaintenanceRequests
GROUP BY [Status];
go 

---8. Total Expenditure on Property Purchases (Buy Table)
USE ResidentialBusiness;
GO
SELECT SUM(TotalExpenditure) AS TotalExpenditure
FROM rb.Buy;
go

---9. Total Number of Residents
USE ResidentialBusiness;
GO
SELECT COUNT(ResidentID) AS TotalResidents
FROM rb.Residents;
go 

---10. Monthly Average Rent Paid by Residents
USE ResidentialBusiness;
GO
SELECT 
    Month(StartDate) AS RentMonth, 
    AVG(Rent) AS AverageMonthlyRent
FROM rb.LeasesToResidents
GROUP BY Month(StartDate)
ORDER BY RentMonth;
go 

----11. Total Revenue and Expenses by Month from ProfitMargin
USE ResidentialBusiness;
GO
SELECT 
    [MONTH], 
    SUM(GrossProfit) AS TotalRevenue, 
    SUM(Expenses) AS TotalExpenses
FROM rb.ProfitMargin
GROUP BY [Month]
ORDER BY [Month];
go 

---12. Top 3 Highest Paid Employees
USE ResidentialBusiness;
GO
SELECT TOP 3 [Name], Position, Salary
FROM rb.Employees
ORDER BY Salary DESC;
go 

--13. Most Expensive Property
USE ResidentialBusiness;
GO
SELECT PropertyName, [Location], Price
FROM rb.Properties
WHERE Price = (SELECT MAX(Price) FROM rb.Properties);
GO

---14. Number of Apartments Available in Each Location
USE ResidentialBusiness;
GO
SELECT p.[Location], COUNT(a.ApartmentID) AS TotalApartments
FROM rb.Properties p
JOIN rb.Apartments a ON p.PropertyID = a.PropertyID
GROUP BY p.[Location];
go 

---15. Count of Maintenance Requests Per Resident
USE ResidentialBusiness;
GO
SELECT r.ResidentName, COUNT(m.RequestID) AS TotalRequests
FROM rb.MaintenanceRequests m
JOIN rb.Residents r ON m.ResidentID = r.ResidentID
GROUP BY r.ResidentName
ORDER BY TotalRequests DESC;
go 

--=================================================Date and Time Functions===============
--using Getdate(), Dateadd(), Datediff()
use ResidentialBusiness
go
select getdate() as CurrentDateTime
go

use ResidentialBusiness
go
select EmployeeID, [Name], HireDate, dateadd(year,1,HireDate) as HireDateNextYear
from rb.Employees
go

use ResidentialBusiness
go
select LeasesToResidentID, ApartmentID, ResidentID, StartDate, EndDate, datediff(day, StartDay, EndDay) as LeaseDurationDays
from rb.LeasesToResidents
go

--==================================================Scalar Functions===============
--using Len(), Substring(), Convert(), Cast()
USE ResidentialBusiness;
GO
--To know the length of every owner 
SELECT OwnerID, OwnerName, LEN(OwnerName) AS NameLength
FROM rb.Owners;
GO


USE ResidentialBusiness;
GO
--To see the first 5 letter of every property
SELECT PropertyID, PropertyName, SUBSTRING(PropertyName, 1, 5) AS ShortName
FROM rb.Properties;
GO

USE ResidentialBusiness;
GO
-- to convert each employee's appointment date to VARCHAR
SELECT EmployeeID, [Name], HireDate, CONVERT(VARCHAR, HireDate, 101) AS HireDateString
FROM rb.Employees;
GO


USE ResidentialBusiness;
GO
-- to convert each property value to FLOAT type
SELECT PropertyID, PropertyName, Price, CAST(Price AS FLOAT) AS PriceAsFloat
FROM rb.Properties;
GO

--================================================String Functions===============
--Using REPLACE(), REVERSE(), LTRIM(), RTRIM()

USE ResidentialBusiness;
GO
-- To replace 'Apartment' with 'Apt' in PropertyName field
SELECT PropertyID, PropertyName, REPLACE(PropertyName, 'Apartment', 'Apt') AS UpdatedPropertyName
FROM rb.Properties;
GO

USE ResidentialBusiness;
GO
--To reverse the characters in the OwnerName field
SELECT OwnerID, OwnerName, REVERSE(OwnerName) AS ReversedOwnerName
FROM rb.Owners;
GO

USE ResidentialBusiness;
GO
--To remove spaces at the beginning of the ResidentName field
SELECT ResidentID, ResidentName, LTRIM(ResidentName) AS TrimmedResidentName
FROM rb.Residents;
GO

USE ResidentialBusiness;
GO
--to remove spaces at the end of the ResidentName field
SELECT ResidentID, ResidentName, RTRIM(ResidentName) AS TrimmedResidentName
FROM rb.Residents;
GO

--===============================================Math Functions===============
--Using ROUND(), ABS(), CEILING(), FLOOR()

USE ResidentialBusiness;
GO
-- To round each property value number to two decimal places
SELECT PropertyID, PropertyName, Price, ROUND(Price, 2) AS RoundedPrice
FROM rb.Properties;
GO

USE ResidentialBusiness;
GO
-- To show the original value of rent for each lease
SELECT LeaseFromOwnerID, Rent, ABS(Rent) AS AbsoluteRent
FROM rb.LeasesFromOwner;
GO

USE ResidentialBusiness;
GO
-- to show the nearest whole number above each property value
SELECT PropertyID, PropertyName, Price, CEILING(Price) AS CeilingPrice
FROM rb.Properties;
GO


USE ResidentialBusiness;
GO
-- to show the nearest whole number below each property value
SELECT PropertyID, PropertyName, Price, FLOOR(Price) AS FloorPrice
FROM rb.Properties;
GO


--===============================================Window Functions==================
--Using ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG()

USE ResidentialBusiness;
GO
SELECT 
    PropertyID, 
    PropertyName, 
    Price,
    ROW_NUMBER() OVER (ORDER BY Price DESC) AS RowNumber
FROM rb.Properties;
GO

USE ResidentialBusiness;
GO
SELECT 
    PropertyID, 
    PropertyName, 
    Price,
    RANK() OVER (ORDER BY Price DESC) AS Rank
FROM rb.Properties;
GO


USE ResidentialBusiness;
GO
SELECT 
    PropertyID, 
    PropertyName, 
    Price,
    DENSE_RANK() OVER (ORDER BY Price DESC) AS DenseRank
FROM rb.Properties;
GO

USE ResidentialBusiness;
GO
SELECT 
    PropertyID, 
    PropertyName, 
    Price,
    LEAD(Price, 1) OVER (ORDER BY Price DESC) AS NextPrice
FROM rb.Properties;
GO

USE ResidentialBusiness;
GO
SELECT 
    PropertyID, 
    PropertyName, 
    Price,
    LAG(Price, 1) OVER (ORDER BY Price DESC) AS PreviousPrice
FROM rb.Properties;
GO

------====================================================CTE Section=============
---CTE in different situation
--1. CTE to Find Total Revenue and Expenses for Each Month
USE ResidentialBusiness;
GO
WITH MonthlyProfit AS (
    SELECT 
        Month,
        SUM(GrossProfit) AS TotalRevenue,
        SUM(Expenses) AS TotalExpenses
    FROM rb.ProfitMargin
    GROUP BY Month
)
SELECT 
    Month,
    TotalRevenue,
    TotalExpenses,
    (TotalRevenue - TotalExpenses) AS NetProfit
FROM MonthlyProfit;
go 

--2. CTE to Find Apartments with Rents Higher than Average
USE ResidentialBusiness;
GO
WITH AverageRent AS (
    SELECT AVG(Rent) AS AvgRent
    FROM rb.LeasesToResidents
)
SELECT 
    a.ApartmentID,
    a.UnitNumber,
    l.Rent,
    ar.AvgRent
FROM rb.Apartments a
JOIN rb.LeasesToResidents l ON a.ApartmentID = l.ApartmentID
CROSS JOIN AverageRent ar
WHERE l.Rent > ar.AvgRent;

---3. CTE to Calculate Total Salary Paid to Employees
USE ResidentialBusiness;
GO
WITH EmployeeSalaries AS (
    SELECT 
        Position, 
        SUM(Salary) AS TotalSalary
    FROM rb.Employees
    GROUP BY Position
)
SELECT 
    Position, 
    TotalSalary
FROM EmployeeSalaries;

--4. CTE to Find Top 5 Properties by Price
USE ResidentialBusiness;
GO
WITH TopProperties AS (
    SELECT 
        PropertyID,
        PropertyName,
        Price,
        ROW_NUMBER() OVER (ORDER BY Price DESC) AS Rank
    FROM rb.Properties
)
SELECT 
    PropertyID,
    PropertyName,
    Price
FROM TopProperties
WHERE Rank <= 5;

--5. CTE to Identify Maintenance Requests Pending by Resident
USE ResidentialBusiness;
GO
WITH PendingRequests AS (
    SELECT 
        m.ResidentID, 
        r.ResidentName, 
        COUNT(m.RequestID) AS TotalPending
    FROM rb.MaintenanceRequests m
    JOIN rb.Residents r ON m.ResidentID = r.ResidentID
    WHERE m.[Status] = 'Pending'
    GROUP BY m.ResidentID, r.ResidentName
)
SELECT 
    ResidentID,
    ResidentName,
    TotalPending
FROM PendingRequests
ORDER BY TotalPending DESC;

--6. CTE to Calculate Total Expenditure by Each Owner
USE ResidentialBusiness;
GO
WITH OwnerExpenditure AS (
    SELECT 
        b.OwnerID, 
        o.OwnerName, 
        SUM(b.TotalExpenditure) AS TotalExpenditure
    FROM rb.Buy b
    JOIN rb.Owners o ON b.OwnerID = o.OwnerID
    GROUP BY b.OwnerID, o.OwnerName
)
SELECT 
    OwnerID, 
    OwnerName, 
    TotalExpenditure
FROM OwnerExpenditure
ORDER BY TotalExpenditure DESC;