SQLize Online / PHPize Online  /  SQLtest Online

-- CREATE TABLE Customers ( -- CustomerID SERIAL PRIMARY KEY, -- FirstName VARCHAR(100), -- LastName VARCHAR(100), -- Email VARCHAR(100) -- ); -- CREATE TABLE AccountTypes ( -- AccountTypeID SERIAL PRIMARY KEY, -- AccountTypeName VARCHAR(100) -- ); -- CREATE TABLE Accounts ( -- AccountID SERIAL PRIMARY KEY, -- CustomerID INT REFERENCES Customers(CustomerID), -- AccountTypeID INT REFERENCES AccountTypes(AccountTypeID), -- Balance DECIMAL(18, 2) -- ); -- CREATE TABLE Transactions ( -- TransactionID SERIAL PRIMARY KEY, -- AccountID INT REFERENCES Accounts(AccountID), -- TransactionDate DATE, -- Amount DECIMAL(18, 2) -- ); -- --Inserting some sample data -- INSERT INTO Customers(FirstName, LastName, Email) VALUES -- ('John', 'Doe', 'john.doe@example.com'), -- ('Jane', 'Doe', 'jane.doe@example.com'), -- ('Jim', 'Smith', 'jim.smith@example.com'); -- INSERT INTO AccountTypes(AccountTypeName) VALUES -- ('Savings'), -- ('Current'); -- INSERT INTO Accounts(CustomerID, AccountTypeID, Balance) VALUES -- (1, 1, 10000.00), -- (1, 2, 5000.00), -- (2, 1, 20000.00), -- (2, 2, 10000.00), -- (3, 1, 15000.00), -- (3, 2, 7500.00); -- INSERT INTO Transactions(AccountID, TransactionDate, Amount) VALUES -- (1, '2023-01-01', 500.00), -- (1, '2023-01-15', -200.00), -- (1, '2023-02-01', 500.00), -- (1, '2023-02-15', -200.00), -- (2, '2023-01-01', 200.00), -- (2, '2023-01-15', -100.00), -- (2, '2023-02-01', 200.00), -- (2, '2023-02-15', -100.00), -- (3, '2023-01-01', 1000.00), -- (3, '2023-01-15', -500.00), -- (3, '2023-02-01', 1000.00), -- (3, '2023-02-15', -500.00); -- CREATE TABLE AccountStatus ( -- AccountStatusID SERIAL PRIMARY KEY, -- AccountID INT REFERENCES Accounts(AccountID), -- Status VARCHAR(50), -- StatusDate DATE -- ); -- --Inserting some additional data -- INSERT INTO Customers(FirstName, LastName, Email) VALUES -- ('Jack', 'Johnson', 'jack.johnson@example.com'), -- ('Jill', 'Johnson', 'jill.johnson@example.com'), -- ('Tom', 'Cruise', 'tom.cruise@example.com'); -- INSERT INTO AccountTypes(AccountTypeName) VALUES -- ('Investment'), -- ('Loan'); -- INSERT INTO Accounts(CustomerID, AccountTypeID, Balance) VALUES -- (4, 1, 30000.00), -- (4, 3, 15000.00), -- (5, 1, 25000.00), -- (5, 4, -10000.00), -- (6, 2, 20000.00), -- (6, 3, 12000.00); -- INSERT INTO Transactions(AccountID, TransactionDate, Amount) VALUES -- (4, '2023-03-01', 1500.00), -- (4, '2023-03-15', -700.00), -- (4, '2023-04-01', 1500.00), -- (4, '2023-04-15', -700.00), -- (5, '2023-03-01', 1250.00), -- (5, '2023-03-15', -600.00), -- (5, '2023-04-01', 1250.00), -- (5, '2023-04-15', -600.00), -- (6, '2023-03-01', 2000.00), -- (6, '2023-03-15', -1000.00), -- (6, '2023-04-01', 2000.00), -- (6, '2023-04-15', -1000.00); -- INSERT INTO AccountStatus(AccountID, Status, StatusDate) VALUES -- (1, 'Active', '2023-01-01'), -- (1, 'Suspended', '2023-02-01'), -- (1, 'Active', '2023-03-01'), -- (2, 'Active', '2023-01-01'), -- (2, 'Closed', '2023-04-01'), -- (3, 'Active', '2023-01-01'), -- (4, 'Active', '2023-02-01'), -- (5, 'Active', '2023-02-01'), -- (6, 'Active', '2023-03-01'); -- Case study questions -- 1. Find the total number of transactions each customer made in 2023. SELECT cust.FirstName, cust.LastName, COUNT(trans.TransactionID)'total number of transactions' FROM Customers cust LEFT JOIN Accounts acct ON cust.CustomerID = acct.CustomerID LEFT JOIN Transactions trans ON acct.AccountID = trans.AccountID GROUP BY cust.FirstName, cust.LastName; -- 2. Calculate the total income (positive transactions) for each customer in 2023. SELECT * FROM Transactions; --3 Find the month with the highest total expenses (negative transactions) in 2023. --4. Calculate the total income, total expenses, and net amount (income - expenses) for each account type for the year 2023. --5. Find the customer with the highest average monthly net transactions (income - expenses) in 2023. --6. For each month in 2023, identify the customer with the highest net transactions (income - expenses) and rank them according to this value.